﻿Imports System.Data.OleDb
Imports System.Threading
Imports System.Collections.Generic
Imports System.ComponentModel

Namespace QuanLyKhachHang
    Public Class frmThongKeSanLuongKhachHang
        Private ReportType As Integer = 0
        Private cm As New Common
        Private groupingList As DataTable
        Private aMaKhachHang As New AutoCompleteStringCollection
#Region "Form Event"
        'end of month
        Private Sub frmThongKeDoanhThu_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            DisableControl()
            dtpTheoNgay.Enabled = True
            Nam_Load()
            Thang_Load()
            Report_TheoNgay()
            AutoComplete_MaKhachHang()
            TinhThanh_Load()
        End Sub
        Protected Sub TinhThanh_Load()
            Try
                Dim controller As New TinhThanhBLL
                Dim list As New List(Of TinhThanh)
                list = controller.TinhThanh_GetItems()
                cbTinhThanh.DataSource = list
                cbTinhThanh.DisplayMember = "TenTinhThanh"
                cbTinhThanh.ValueMember = "TinhThanhID"
            Catch ex As Exception

            End Try
        End Sub
        Protected Function DayToInteger(ByVal year As Integer, ByVal month As Integer) As Integer
            Try

            Catch ex As Exception

            End Try
        End Function
        Sub DisableControl()
            dtpTheoNgay.Enabled = False
            cbxTheoThang.Enabled = False
            cbxNamThang.Enabled = False
            cbxTheoNam.Enabled = False
            dtpTuNgay.Enabled = False
            dtpDenNgay.Enabled = False
        End Sub


        Private Sub rbtTheoNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNgay.CheckedChanged
            DisableControl()
            dtpTheoNgay.Enabled = True
            dtpTheoNgay.Value = Now.Date
            ReportType = 0
        End Sub
        Private IsPostBack As Boolean = False
        Private Sub rbtTheoThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoThang.CheckedChanged
            DisableControl()
            cbxTheoThang.Enabled = True
            If IsPostBack = False Then
                cbxTheoThang.SelectedIndex = IndexCurrentMonth
            End If
            cbxNamThang.Enabled = True
            If IsPostBack = False Then
                cbxNamThang.SelectedIndex = 0
            End If
            ReportType = 1
            IsPostBack = True
        End Sub
        Private IsPostBack2 As Boolean = False
        Private Sub rbtTheoNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNam.CheckedChanged
            DisableControl()
            cbxTheoNam.Enabled = True
            If IsPostBack2 = False Then
                cbxTheoNam.SelectedIndex = 0
            End If
            ReportType = 2
            IsPostBack2 = True
        End Sub

        Private Sub rbtTuNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTuNgay.CheckedChanged
            DisableControl()
            dtpTuNgay.Enabled = True
            dtpTuNgay.Value = Now.Date
            dtpDenNgay.Enabled = True
            dtpDenNgay.Value = Now.Date
            ReportType = 3
        End Sub
        Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
            'clear grid
            dgvThongKeDoanhThu.Rows.Clear()
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
                Case 4
                    Report_TheoTenKhachHang()
            End Select
        End Sub

        'Function ConvertDateToInt(ByVal Ngay As String)
        '    Dim aNgay As String() = Ngay.Split("/")
        '    'Dim iThang As String
        '    'Dim iNgay As String
        '    Dim Ngay_So As String
        '    'If Integer.Parse(aNgay(1)) <= 9 Then
        '    '    iNgay = "0" & aNgay(1)
        '    'Else
        '    '    iNgay = aNgay(1)
        '    'End If
        '    'If Integer.Parse(aNgay(0)) <= 9 Then
        '    '    iThang = "0" & aNgay(0)
        '    'Else
        '    '    iThang = aNgay(0)
        '    'End If
        '    Ngay_So = aNgay(2) & aNgay(0) & aNgay(1)
        '    Try
        '        Return Integer.Parse(Ngay_So)
        '    Catch ex As Exception
        '        Return 0
        '    End Try
        'End Function

        Function formatDate_VN(ByRef Ngay As String) As Boolean
            Dim aNgay As String() = Ngay.Split("/")
            Ngay = aNgay(1) & "/" & aNgay(0) & "/" & aNgay(2)
        End Function
        Function TinhTongCong(ByVal dtTable As DataTable) As String
            Dim SoTien As Integer = 0
            For i As Integer = 0 To dtTable.Rows.Count - 1
                SoTien = SoTien + ConvertCurencyToInt(dtTable.Rows(i)("ThanhTien"))
            Next
            Return CDec(SoTien.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
        End Function
        Function ConvertCurencyToInt(ByVal Curency As String) As Integer
            Dim retInt As Integer
            If Curency = "" Then
                Return 0
            Else
                Curency = Curency.Split(",")(0).Replace(".", "")
                retInt = Integer.Parse(Curency)
                Return retInt
            End If
        End Function
        Protected Function CreateDataTableTong() As DataTable
            Dim dtTable As DataTable = New DataTable()
            Try
                Dim Tong As DataColumn = New DataColumn("TongCong")
                Tong.DataType = System.Type.GetType("System.String")
                dtTable.Columns.Add(Tong)
                Return dtTable
            Catch ex As Exception
                Throw
            End Try
        End Function
#End Region
#Region "Thống kê - báo cáo"
        '--------Tính tiền doanh thu của khách hàng trên 1 đơn hàng-------------------
        Private Function TinhSanLuongKhachHangTheoDonHang(ByVal IdDonHangBan As Integer) As Single
            Try
                'Truyen MaKhachHang De Lay Thong Tin
                Dim sRet As Single = 0
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dbCommand As OleDb.OleDbCommand
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                dbCommand = New OleDbCommand("spTinhSanLuongTieuThuTheoDonHang", dbConn)
                dbCommand.CommandType = CommandType.StoredProcedure
                dbCommand.Parameters.Add("@IDDonHangBan", OleDbType.Integer).Value = IdDonHangBan
                sRet = dbCommand.ExecuteScalar()
                dbConn.Close()
                Return sRet
            Catch ex As Exception
                MessageBox.Show("Có lỗi trong quá trình xử lý.Vui lòng thử lại")
            End Try
        End Function
        '--------Tính tiền doanh thu của khách hàng trên 1 đơn hàng-------------------
        Private Function TinhDoanhThuCongTyTheoDonHang(ByVal IdDonHangBan As Integer) As Single
            Try
                'Return sRet
                Dim sRet As Single = 0
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dbCommand As OleDb.OleDbCommand
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                dbCommand = New OleDbCommand("spTinhDoanhThuTheoDonHang", dbConn)
                dbCommand.CommandType = CommandType.StoredProcedure
                dbCommand.Parameters.Add("@IdDonHangBan", OleDbType.Integer).Value = IdDonHangBan
                dbCommand.Parameters.Add("@LoaiPhi", OleDbType.VarChar).Value = "GiamTru"
                sRet = dbCommand.ExecuteScalar()
                dbConn.Close()
                Return sRet
            Catch ex As Exception
                MessageBox.Show("Có lỗi trong quá trình xử lý.Vui lòng thử lại")
            End Try
        End Function


        Private Function TinhTongDoanhThuKhachHang(ByVal KhachHangId As Integer, ByVal CalOption As Integer, Optional ByVal sSQLMuaHang As String = "", Optional ByVal sSQLDaiLy As String = "")
            Try

            Catch ex As Exception

            End Try
        End Function
        Private Function TinhTongDoanhThuCongTy(ByVal KhachHangId As Integer, ByVal CalOption As Integer, Optional ByVal sSQLMuaHang As String = "", Optional ByVal sSQLDaiLy As String = "")
            Dim sRet As Single = 0
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dtThanhTien As New DataTable
                Dim query_Tien_HoaDon As String
                Dim adapter As OleDb.OleDbDataAdapter
                Dim TongTienHoaDon As Integer = 0

                If rbtTatCa.Checked Then
                    query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai<>N'Đã hủy' And LoaiPhi='GiamTru' And KhachHangId={0} " & sSQLMuaHang, KhachHangId)
                ElseIf rbtChuaDuyet.Checked Then
                    query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai<>N'Đã hủy' And TrangThai=N'Chưa duyệt' And LoaiPhi='GiamTru' And KhachHangId={0} " & sSQLMuaHang, KhachHangId)
                ElseIf rbtDaDuyet.Checked Then
                    query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Đã duyệt' And LoaiPhi='GiamTru' And KhachHangId={0} " & sSQLMuaHang, KhachHangId)
                End If
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
                adapter.Fill(dtThanhTien)
                For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                    sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
                Next
                'Tinh Thêm Tiền Làm Đại Lý
                dtThanhTien = New DataTable
                'query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Đã duyệt' And LoaiPhi='GiamTru' And IDDonHangBan In (Select IDDonHangBan From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Đã duyệt' And NhanVienTiepThiId={0} And NgayBan='2/19/2011')", KhachHangId)

                If rbtTatCa.Checked Then
                    query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai<>N'Đã hủy' And LoaiPhi='GiamTru' " & sSQLDaiLy, KhachHangId)
                ElseIf rbtChuaDuyet.Checked Then
                    query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Chưa duyệt' And LoaiPhi='GiamTru' " & sSQLDaiLy, KhachHangId)
                ElseIf rbtDaDuyet.Checked Then
                    query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where TrangThai=N'Đã duyệt' And LoaiPhi='GiamTru' " & sSQLDaiLy, KhachHangId)
                End If
                adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
                adapter.Fill(dtThanhTien)
                For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                    sRet = sRet + dtThanhTien.Rows(i)("ThanhTien")
                Next
                dbConn.Close()
                Return sRet
            Catch ex As Exception
                Throw
            End Try
        End Function
#End Region
#Region "Thống kê theo ngày"
        Protected Function Create_GroupingList() As DataTable
            Dim dtTable As DataTable = New DataTable()
            Try

                Dim TenKhachHang As DataColumn = New DataColumn("TenKhachHang")
                TenKhachHang.DataType = System.Type.GetType("System.String")
                TenKhachHang.DefaultValue = ""
                dtTable.Columns.Add(TenKhachHang)

                Dim MaSoThue As DataColumn = New DataColumn("MaSoThue")
                MaSoThue.DataType = System.Type.GetType("System.String")
                MaSoThue.DefaultValue = ""
                dtTable.Columns.Add(MaSoThue)

                Dim DiaChi As DataColumn = New DataColumn("DiaChi")
                DiaChi.DataType = System.Type.GetType("System.String")
                DiaChi.DefaultValue = ""
                dtTable.Columns.Add(DiaChi)

                Dim NgayBan As DataColumn = New DataColumn("NgayBan")
                NgayBan.DataType = System.Type.GetType("System.String")
                dtTable.Columns.Add(NgayBan)

                Dim So As DataColumn = New DataColumn("So")
                So.DataType = System.Type.GetType("System.String")
                dtTable.Columns.Add(So)

                Dim DoanhThu As DataColumn = New DataColumn("SanLuong")
                DoanhThu.DataType = System.Type.GetType("System.String")
                DoanhThu.DefaultValue = ""
                dtTable.Columns.Add(DoanhThu)

                Dim NguonDoanhThu As DataColumn = New DataColumn("NguonDoanhThu")
                NguonDoanhThu.DataType = System.Type.GetType("System.String")
                NguonDoanhThu.DefaultValue = ""
                dtTable.Columns.Add(NguonDoanhThu)

                Dim NhanVienTiepThi As DataColumn = New DataColumn("NhanVienTiepThi")
                NhanVienTiepThi.DataType = System.Type.GetType("System.String")
                NhanVienTiepThi.DefaultValue = ""
                dtTable.Columns.Add(NhanVienTiepThi)

                Dim IDDonHangBan As DataColumn = New DataColumn("IDDonHangBan")
                IDDonHangBan.DataType = System.Type.GetType("System.String")
                IDDonHangBan.DefaultValue = ""
                dtTable.Columns.Add(IDDonHangBan)

                Return dtTable
            Catch ex As Exception
                Throw
            End Try
        End Function
        Sub Report_TheoNgay()
            Try
                Dim TuNgay As Integer = ConvertDateToInt(dtpTheoNgay.Value.ToString.Split(" ")(0))
                Dim DenNgay As Integer = ConvertDateToInt(dtpTheoNgay.Value.ToString.Split(" ")(0))
                ThongKeDoanhThu(TuNgay, DenNgay)
            Catch ex As Exception
                ShowError(ex)
            End Try
        End Sub
#End Region
#Region "Report Theo Tháng"
        Private Sub Report_TheoThang()
            Dim Thang As MonthInfo
            Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
            If Thang Is Nothing Then
                MessageBox.Show("Bạn chưa chọn tháng.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim Nam As YearInfo
            Nam = CType(cbxNamThang.SelectedItem, YearInfo)
            If Nam Is Nothing Then
                MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim TuNgay As Integer
            Dim DenNgay As Integer
            Dim sNgayCuoiThang As String
            Dim sThang As String
            Dim DaysInMonth As Integer = DateTime.DaysInMonth(Nam.TenNam, Thang.TenThang)
            If DaysInMonth <= 9 Then
                sNgayCuoiThang = "0" & DaysInMonth.ToString()
            Else
                sNgayCuoiThang = DaysInMonth.ToString()
            End If
            If Integer.Parse(Thang.TenThang) <= 9 Then
                sThang = "0" & Thang.TenThang
            Else
                sThang = Thang.TenThang
            End If
            TuNgay = Integer.Parse(Nam.TenNam & sThang & "01")
            DenNgay = Integer.Parse(Nam.TenNam & sThang & sNgayCuoiThang)
            ThongKeDoanhThu(TuNgay, DenNgay)
        End Sub
#End Region
#Region "Report theo năm"
        Sub Report_TheoNam()
            Dim Nam As YearInfo
            Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
            If Nam Is Nothing Then
                MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim TuNgay As Integer = Integer.Parse(Nam.TenNam & "01" & "01")
            Dim DenNgay As Integer = Integer.Parse(Nam.TenNam & "12" & "30")
            ThongKeDoanhThu(TuNgay, DenNgay)
        End Sub
#End Region
#Region "Report từ ngày đến ngày"
        Private dtExportExcel As New DataTable
        Sub Report_TuNgayDenNgay()
            '2010007004  -->20100704
            Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
            Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
            If DenNgay < TuNgay Then
                Dim dlgRes As DialogResult
                dlgRes = MessageBox.Show( _
                          "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                          "Thông báo", _
                    MessageBoxButtons.OK, _
                          MessageBoxIcon.Question)
                If dlgRes = Windows.Forms.DialogResult.OK Then
                    Exit Sub
                End If
            End If
            ThongKeDoanhThu(TuNgay, DenNgay)
        End Sub
#End Region
#Region "Report theo tên khách hàng"
        Function IsNotExist(ByVal TenKhachHang As String) As Boolean
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsKhachHang As New DataSet
                Dim dtKhachHang As New DataTable
                Dim query As String = "Select Count(*) From DanhMucKhachHang Where LOWER(TenKhachHang)=N'" & TenKhachHang & "'"
                Dim dbCommand As OleDb.OleDbCommand
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                dbCommand = New OleDb.OleDbCommand(query, dbConn)
                Dim iResult As Integer
                iResult = dbCommand.ExecuteScalar()
                If iResult = 0 Then
                    Return True
                Else
                    Return False
                End If
                dbConn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Function
        Private Sub Report_TheoTenKhachHang()
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            ''check Thông tin Khách Hàng
            ''End Check
            'Dim query As String = ""
            'REM Grouping list
            'groupingList = Create_GroupingList()
            ''title
            'Dim dtTenKhachHang As New DataTable
            'If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
            '    If rbtTatCa.Checked Then
            '        query = "Select Distinct TenNguoiMua,TenKhachHang,KhachHangId From DonHangBan Where TrangThai<>N'Đã hủy' And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By TenKhachHang"
            '    ElseIf rbtChuaDuyet.Checked Then
            '        query = "Select Distinct TenNguoiMua,TenKhachHang,KhachHangId From DonHangBan Where TrangThai=N'Chưa duyệt' And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By TenKhachHang"
            '    ElseIf rbtDaDuyet.Checked Then
            '        query = "Select Distinct TenNguoiMua,TenKhachHang,KhachHangId From DonHangBan Where TrangThai=N'Đã duyệt' And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By TenKhachHang"
            '    End If
            'Else
            '    If rbtTatCa.Checked Then
            '        query = "Select Distinct TenNguoiMua,TenKhachHang,KhachHangId From DonHangBan Where TrangThai<>N'Đã hủy' And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By TenKhachHang"
            '    ElseIf rbtChuaDuyet.Checked Then
            '        query = "Select Distinct TenNguoiMua,TenKhachHang,KhachHangId From DonHangBan Where TrangThai=N'Chưa duyệt' And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By TenKhachHang"
            '    ElseIf rbtDaDuyet.Checked Then
            '        query = "Select Distinct TenNguoiMua,TenKhachHang,KhachHangId From DonHangBan Where TrangThai=N'Đã duyệt' And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By TenKhachHang"
            '    End If
            'End If
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtTenKhachHang)
            'Dim TongDoanhThu As Integer
            'Dim TongDoanhThuCTy As Integer
            'For i As Integer = 0 To dtTenKhachHang.Rows.Count - 1
            '    'title
            '    Dim sSQL As String
            '    If rbtTatCa.Checked Then
            '        sSQL = "And IDDonHangBan In (Select IDDonHangBan From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai<>N'Đã hủy' And NhanVienTiepThiId={0}" & ")"
            '    ElseIf rbtChuaDuyet.Checked Then
            '        sSQL = "And IDDonHangBan In (Select IDDonHangBan From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Chưa duyệt' And NhanVienTiepThiId={0}" & ")"
            '    ElseIf rbtDaDuyet.Checked Then
            '        sSQL = "And IDDonHangBan In (Select IDDonHangBan From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Đã duyệt' And NhanVienTiepThiId={0}" & ")"
            '    End If

            '    Dim titleRow As DataRow
            '    titleRow = groupingList.NewRow
            '    titleRow.Item("TenKhachHang") = IIf(dtTenKhachHang.Rows(i)("TenKhachHang") = "", dtTenKhachHang.Rows(i)("TenNguoiMua"), dtTenKhachHang.Rows(i)("TenKhachHang"))
            '    titleRow.Item("MaSoThue") = ""
            '    titleRow.Item("DiaChi") = ""
            '    titleRow.Item("NgayBan") = ""
            '    titleRow.Item("So") = ""
            '    titleRow.Item("DoanhThu") = ""
            '    titleRow.Item("DoanhThuCTy") = ""
            '    titleRow.Item("NguonDoanhThu") = ""
            '    groupingList.Rows.Add(titleRow)
            '    'detail
            '    If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
            '        If rbtTatCa.Checked Then
            '            query = "Select IdDonHangBan,TenKhachHang,MaSoThue,DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan Where TrangThai<>N'Đã hủy' And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
            '        ElseIf rbtChuaDuyet.Checked Then
            '            query = "Select IdDonHangBan,TenKhachHang,MaSoThue,DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan Where TrangThai=N'Chưa duyệt' And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
            '        ElseIf rbtDaDuyet.Checked Then
            '            query = "Select IdDonHangBan,TenKhachHang,MaSoThue,DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan Where TrangThai=N'Đã duyệt' And TenKhachHang Like '" & txtTenKhachHang.Text.Trim & "' Order By Ngay_So DESC"
            '        End If
            '    Else
            '        If rbtTatCa.Checked Then
            '            query = "Select IdDonHangBan,TenKhachHang,MaSoThue,DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan Where TrangThai<>N'Đã hủy' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
            '        ElseIf rbtChuaDuyet.Checked Then
            '            query = "Select IdDonHangBan,TenKhachHang,MaSoThue,DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan Where TrangThai=N'Chưa duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
            '        ElseIf rbtDaDuyet.Checked Then
            '            query = "Select IdDonHangBan,TenKhachHang,MaSoThue,DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan Where TrangThai=N'Đã duyệt' And KhachHangId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " And KhachHangId=" & lblKhachHangId.Text.Trim & " Order By Ngay_So DESC"
            '        End If
            '    End If

            '    Dim dtThongKe As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtThongKe)
            '    dtThongKe.Columns.Add("DoanhThu")
            '    dtThongKe.Columns.Add("DoanhThuCTy")
            '    Dim sThanhTien As String
            '    Dim DoanhThu As Single = 0
            '    Dim DoanhThuCTy As Single = 0
            '    Dim ThongTin As String
            '    Dim IDKhachHang As Integer
            '    Dim dbCommand As OleDb.OleDbCommand
            '    'Mua Truc Tiep
            '    For j As Integer = 0 To dtThongKe.Rows.Count - 1
            '        'Tính Doanh Thu Khách Hàng
            '        DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(j)("IdDonHangBan"))
            '        TongDoanhThu = TongDoanhThu + DoanhThu
            '        'Tính doanh Thu Cty
            '        DoanhThuCTy = DoanhThu - TinhDoanhThuCongTyTheoDonHang(dtThongKe.Rows(j)("IdDonHangBan"))
            '        TongDoanhThuCTy = TongDoanhThuCTy + DoanhThuCTy
            '        sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '        dtThongKe.Rows(j)("DoanhThu") = sThanhTien
            '        sThanhTien = CDec(DoanhThuCTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '        dtThongKe.Rows(j)("DoanhThuCTy") = sThanhTien
            '        formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
            '        'detail row
            '        Dim detailRow As DataRow
            '        detailRow = groupingList.NewRow
            '        detailRow.Item("TenKhachHang") = ""
            '        detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
            '        detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
            '        detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
            '        detailRow.Item("So") = dtThongKe.Rows(j)("So")
            '        detailRow.Item("DoanhThu") = dtThongKe.Rows(j)("DoanhThu")
            '        detailRow.Item("DoanhThuCTy") = dtThongKe.Rows(j)("DoanhThuCTy")
            '        If dtThongKe.Rows(j)("HinhThucTiepThi") = "Đại lý" Then
            '            IDKhachHang = Integer.Parse(dtThongKe.Rows(j)("IdDonHangBan"))
            '            dbCommand = New OleDbCommand("spThongTinTiepThi", dbConn)
            '            dbCommand.CommandType = CommandType.StoredProcedure
            '            dbCommand.Parameters.Add("@IDDonHangBan", OleDbType.Integer).Value = IDKhachHang
            '            ThongTin = dbCommand.ExecuteScalar().ToString()
            '            detailRow.Item("NguonDoanhThu") = "Mua hàng qua đại lý " & ThongTin
            '        Else
            '            detailRow.Item("NguonDoanhThu") = "Mua hàng trực tiếp"
            '        End If
            '        groupingList.Rows.Add(detailRow)
            '    Next

            '    'Them Cac Dong Ma Doanh Thu Tu Lam Dai Ly
            '    If rbtTatCa.Checked Then
            '        'query = "Select * From DonHangBan Where HinhThucTiepThi=N'Đại lý' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " Order By Ngay_So DESC"
            '        query = "Select IdDonHangBan,DH.TenKhachHang As TiepThiChoKhachHang,DH.TenNguoiMua,KH.TenKhachHang,KH.MaSoThue,KH.DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan As DH" & _
            '            " Left Outer Join DanhMucKhachHang As KH On DH.NhanVienTiepThiId = KH.KhachHangId" & _
            '            " Where DH.HinhThucTiepThi=N'Đại lý' And DH.TrangThai<>N'Đã hủy' And DH.NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " Order By Ngay_So DESC"
            '    ElseIf rbtChuaDuyet.Checked Then
            '        'query = "Select * From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Chưa duyệt' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " Order By Ngay_So DESC"
            '        query = "Select IdDonHangBan,DH.TenKhachHang As TiepThiChoKhachHang,DH.TenNguoiMua,KH.TenKhachHang,KH.MaSoThue,KH.DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan As DH" & _
            '            " Left Outer Join DanhMucKhachHang As KH On DH.NhanVienTiepThiId = KH.KhachHangId" & _
            '            " Where DH.HinhThucTiepThi=N'Đại lý' And DH.TrangThai=N'Chưa duyệt' And DH.NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " Order By Ngay_So DESC"
            '    ElseIf rbtDaDuyet.Checked Then
            '        'query = "Select * From DonHangBan Where HinhThucTiepThi=N'Đại lý' And TrangThai=N'Đã duyệt' And NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " Order By Ngay_So DESC"
            '        query = "Select IdDonHangBan,DH.TenKhachHang As TiepThiChoKhachHang,DH.TenNguoiMua,KH.TenKhachHang,KH.MaSoThue,KH.DiaChi,NgayBan,So,HinhThucTiepThi From DonHangBan As DH" & _
            '            " Left Outer Join DanhMucKhachHang As KH On DH.NhanVienTiepThiId = KH.KhachHangId" & _
            '            " Where DH.HinhThucTiepThi=N'Đại lý' And DH.TrangThai=N'Đã duyệt' And DH.NhanVienTiepThiId=" & dtTenKhachHang.Rows(i)("KhachHangId") & " Order By Ngay_So DESC"
            '    End If
            '    dtThongKe = New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtThongKe)
            '    dtThongKe.Columns.Add("DoanhThu")
            '    dtThongKe.Columns.Add("DoanhThuCTy")
            '    sThanhTien = ""
            '    For j As Integer = 0 To dtThongKe.Rows.Count - 1
            '        'Tính Doanh Thu Khách Hàng
            '        DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(j)("IdDonHangBan"))
            '        TongDoanhThu = TongDoanhThu + DoanhThu
            '        'Tính doanh Thu Cty
            '        DoanhThuCTy = DoanhThu - TinhDoanhThuCongTyTheoDonHang(dtThongKe.Rows(j)("IdDonHangBan"))
            '        TongDoanhThuCTy = TongDoanhThuCTy + DoanhThuCTy
            '        sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '        dtThongKe.Rows(j)("DoanhThu") = sThanhTien
            '        sThanhTien = CDec(DoanhThuCTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '        dtThongKe.Rows(j)("DoanhThuCTy") = sThanhTien
            '        formatDate2VN(dtThongKe.Rows(j)("NgayBan"))
            '        'detail row
            '        Dim detailRow As DataRow
            '        detailRow = groupingList.NewRow
            '        detailRow.Item("TenKhachHang") = ""
            '        detailRow.Item("MaSoThue") = dtThongKe.Rows(j)("MaSoThue")
            '        detailRow.Item("DiaChi") = dtThongKe.Rows(j)("DiaChi")
            '        detailRow.Item("NgayBan") = dtThongKe.Rows(j)("NgayBan")
            '        detailRow.Item("So") = dtThongKe.Rows(j)("So")
            '        detailRow.Item("DoanhThu") = dtThongKe.Rows(j)("DoanhThu")
            '        detailRow.Item("DoanhThuCTy") = dtThongKe.Rows(j)("DoanhThuCTy")
            '        detailRow.Item("NguonDoanhThu") = "Đại lý tiếp thị cho " & IIf(dtThongKe.Rows(j)("TiepThiChoKhachHang") = "", dtThongKe.Rows(j)("TenNguoiMua"), dtThongKe.Rows(j)("TiepThiChoKhachHang"))
            '        groupingList.Rows.Add(detailRow)
            '    Next
            '    Dim SumRow As DataRow
            '    SumRow = groupingList.NewRow
            '    SumRow.Item("TenKhachHang") = ""
            '    SumRow.Item("MaSoThue") = ""
            '    SumRow.Item("DiaChi") = ""
            '    SumRow.Item("NgayBan") = ""
            '    SumRow.Item("So") = "Tổng :"
            '    SumRow.Item("DoanhThu") = CDec(TongDoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '    SumRow.Item("DoanhThuCTy") = CDec(TongDoanhThuCTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '    SumRow.Item("NguonDoanhThu") = ""
            '    groupingList.Rows.Add(SumRow)
            '    'update row i
            'Next
            'dbConn.Close()
            ''bind data
            'dgvThongKeDoanhThu.Rows.Clear()
            'dgvThongKeDoanhThu.ColumnsCount = 8
            ''tạo header 
            'dgvThongKeDoanhThu.FixedRows = 1
            'dgvThongKeDoanhThu.Rows.Insert(0)
            'dgvThongKeDoanhThu(0, 0) = New SourceGrid.Cells.ColumnHeader("Tên khách hàng")
            'dgvThongKeDoanhThu(0, 0).Column.Width = 110
            'dgvThongKeDoanhThu(0, 1) = New SourceGrid.Cells.ColumnHeader("Mã số thuế")
            'dgvThongKeDoanhThu(0, 1).Column.Width = 100
            'dgvThongKeDoanhThu(0, 2) = New SourceGrid.Cells.ColumnHeader("Địa chỉ")
            'dgvThongKeDoanhThu(0, 2).Column.Width = 270
            'dgvThongKeDoanhThu(0, 3) = New SourceGrid.Cells.ColumnHeader("Ngày bán")
            'dgvThongKeDoanhThu(0, 3).Column.Width = 70
            'dgvThongKeDoanhThu(0, 4) = New SourceGrid.Cells.ColumnHeader("Số đơn hàng")
            'dgvThongKeDoanhThu(0, 4).Column.Width = 80
            'dgvThongKeDoanhThu(0, 5) = New SourceGrid.Cells.ColumnHeader("Doanh thu")
            'dgvThongKeDoanhThu(0, 5).Column.Width = 100
            'dgvThongKeDoanhThu(0, 6) = New SourceGrid.Cells.ColumnHeader("Doanh thu công ty")
            'dgvThongKeDoanhThu(0, 6).Column.Width = 100
            'dgvThongKeDoanhThu(0, 7) = New SourceGrid.Cells.ColumnHeader("Nguồn doanh thu")
            'dgvThongKeDoanhThu(0, 7).Column.Width = 250
            ''tạo detail
            'Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
            'rowCOLSPAN.BackColor = Color.FromArgb(192, 255, 192)
            'Dim StyleLeft As New SourceGrid.Cells.Views.Cell
            'StyleLeft.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleLeft
            'Dim StyleRight As New SourceGrid.Cells.Views.Cell
            'StyleRight.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
            'For i As Integer = 0 To groupingList.Rows.Count - 1
            '    dgvThongKeDoanhThu.Rows.Insert(i + 1)
            '    If groupingList.Rows(i)("TenKhachHang").ToString <> "" Then
            '        dgvThongKeDoanhThu(i + 1, 0) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("TenKhachHang").ToString)
            '        dgvThongKeDoanhThu(i + 1, 0).ColumnSpan = 8
            '        dgvThongKeDoanhThu(i + 1, 0).View = rowCOLSPAN
            '    Else
            '        dgvThongKeDoanhThu(i + 1, 0) = New SourceGrid.Cells.Cell()
            '        dgvThongKeDoanhThu(i + 1, 1) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("MaSoThue").ToString)
            '        dgvThongKeDoanhThu(i + 1, 1).View = StyleLeft
            '        dgvThongKeDoanhThu(i + 1, 2) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("DiaChi").ToString)
            '        dgvThongKeDoanhThu(i + 1, 2).View = StyleLeft
            '        dgvThongKeDoanhThu(i + 1, 3) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("NgayBan").ToString)
            '        dgvThongKeDoanhThu(i + 1, 3).View = StyleRight
            '        dgvThongKeDoanhThu(i + 1, 4) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("So").ToString)
            '        dgvThongKeDoanhThu(i + 1, 4).View = StyleRight
            '        dgvThongKeDoanhThu(i + 1, 5) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("DoanhThu").ToString)
            '        dgvThongKeDoanhThu(i + 1, 5).View = StyleRight
            '        dgvThongKeDoanhThu(i + 1, 6) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("DoanhThuCTy").ToString)
            '        dgvThongKeDoanhThu(i + 1, 6).View = StyleRight
            '        dgvThongKeDoanhThu(i + 1, 7) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("NguonDoanhThu").ToString)
            '        dgvThongKeDoanhThu(i + 1, 7).View = StyleLeft
            '        '
            '    End If
            'Next
        End Sub
#End Region
        '#Region "Report theo tên khách hàng"
        '    REM Tìm kiếm tên khách hàng
        '    Private MaKhachHang As String
        '    Sub KhachHang_Search()
        '        Try
        '            Dim dbConn As New OleDb.OleDbConnection
        '            Dim dataAccess As New DataAccess
        '            Dim dsKhachHang As New DataSet
        '            Dim dtKhachHang As New DataTable
        '            Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtTenKhachHang_Search.Text.Trim & "%'"
        '            Dim adapter As OleDb.OleDbDataAdapter
        '            dataAccess.ConnectDatabase_DBAccess(dbConn)
        '            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        '            adapter.Fill(dsKhachHang)
        '            dbConn.Close()
        '            dtKhachHang = dsKhachHang.Tables(0)
        '            Panel_KhachHang_Search.Visible = True
        '            dgvKhachHang_Search.AutoGenerateColumns = False
        '            dgvKhachHang_Search.DataSource = dtKhachHang
        '        Catch ex As Exception
        '            MessageBox.Show(ex.ToString())
        '        End Try
        '    End Sub

        '    Private Sub txtTenKhachHang_Search_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang_Search.KeyUp
        '        Try
        '            If txtTenKhachHang_Search.Text <> "" Then
        '                KhachHang_Search()
        '            Else
        '                Panel_KhachHang_Search.Visible = False
        '            End If
        '        Catch ex As Exception
        '            MessageBox.Show(ex.ToString())
        '        End Try
        '    End Sub
        '    Private Sub dgvKhachHang_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvKhachHang_Search.CellDoubleClick
        '        Try
        '            txtTenKhachHang_Search.Text = dgvKhachHang_Search.Rows(e.RowIndex).Cells("TenKhachHang_S").Value.ToString()
        '            MaKhachHang = dgvKhachHang_Search.Rows(e.RowIndex).Cells("KhachHangId_ThongKe").Value.ToString()
        '            Panel_KhachHang_Search.Visible = False
        '            cmdThongKe.Focus()
        '        Catch ex As Exception

        '        End Try
        '    End Sub

        '    Private Sub cmdThongKe_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdThongKe.Enter
        '        Try
        '            Panel_KhachHang_Search.Visible = False
        '        Catch ex As Exception

        '        End Try
        '    End Sub
        '    Sub Report_TenKhachHang()
        '        Dim dbConn As New OleDb.OleDbConnection
        '        Dim dataAccess As New DataAccess
        '        'Check Tên Khách Hàng
        '        If IsNotExist(txtTenKhachHang_Search.Text.ToString.Trim) Then
        '            MessageBox.Show("Tên khách hàng bạn nhập chưa có trong danh mục.Vui lòng kiểm tra lại", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
        '            txtTenKhachHang_Search.Focus()
        '        End If
        '        REM end checking 
        '        Dim query As String = "Select * From DonHangBan Where KhachHangId='" & MaKhachHang & "'" & " Order by Ngay_So DESC"
        '        Dim dtThongKe As New DataTable
        '        Dim adapter As OleDb.OleDbDataAdapter
        '        dataAccess.ConnectDatabase_DBAccess(dbConn)
        '        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        '        adapter.Fill(dtThongKe)
        '        dbConn.Close()
        '        dtThongKe.Columns.Add("DoanhThu")
        '        dtThongKe.Columns.Add("STT")
        '        Dim sThanhTien As String
        '        Dim DoanhThu As Single = 0
        '        For i As Integer = 0 To dtThongKe.Rows.Count - 1
        '            'Tính Doanh Thu Khách Hàng
        '            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
        '            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
        '            dtThongKe.Rows(i)("DoanhThu") = sThanhTien
        '            dtThongKe.Rows(i)("STT") = i + 1
        '            formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        '        Next
        '        dgvKhachHang.AutoGenerateColumns = False
        '        dgvKhachHang.DataSource = dtThongKe
        '        If dgvKhachHang.Rows.Count - 1 > 0 Then
        '            dgvKhachHang.Rows(0).Selected = True
        '        End If
        '    End Sub
        '#End Region


        Private Sub cmdInThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Try
                Select Case ReportType
                    Case 0
                        frmInThongKeDoanhThu.ReportType = 0
                        frmInThongKeDoanhThu.Ngay = dtpTheoNgay.Value.ToShortDateString
                        frmInThongKeDoanhThu.Show()
                    Case 1
                        frmInThongKeDoanhThu.ReportType = 1
                        Dim Thang As MonthInfo
                        Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                        frmInThongKeDoanhThu.Thang = Thang.TenThang
                        frmInThongKeDoanhThu.Show()
                    Case 2
                        frmInThongKeDoanhThu.ReportType = 2
                        Dim Nam As YearInfo
                        Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                        frmInThongKeDoanhThu.Nam = Nam.TenNam
                        frmInThongKeDoanhThu.Show()
                    Case 3
                        frmInThongKeDoanhThu.ReportType = 3
                        Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
                        Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
                        If DenNgay < TuNgay Then
                            Dim dlgRes As DialogResult
                            dlgRes = MessageBox.Show( _
                                      "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                                      "Thông báo", _
                                MessageBoxButtons.OK, _
                                      MessageBoxIcon.Question)
                            If dlgRes = Windows.Forms.DialogResult.OK Then
                                Exit Sub
                            End If
                        End If
                        frmInThongKeDoanhThu.TuNgay = TuNgay
                        frmInThongKeDoanhThu.DenNgay = DenNgay
                        frmInThongKeDoanhThu.sTuNgay = dtpTuNgay.Value.ToShortDateString
                        frmInThongKeDoanhThu.sDenNgay = dtpDenNgay.Value.ToShortDateString
                        frmInThongKeDoanhThu.Show()
                End Select
            Catch ex As Exception
                ShowError()
            End Try
        End Sub
#Region "Sub Class"
        Private Class YearInfo
            Dim _Nam As Integer
            Dim _TenNam As String
            Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
                _Nam = Nam
                _TenNam = TenNam
            End Sub
            Public Function Nam() As Integer
                Return _Nam
            End Function
            Public Function TenNam() As String
                Return _TenNam
            End Function
            Public Overrides Function ToString() As String
                Return _TenNam
            End Function
        End Class
        Private Class MonthInfo
            Dim _Thang As Integer
            Dim _TenThang As String
            Public Sub New(ByVal Thang As Integer, ByVal TenThang As String)
                _Thang = Thang
                _TenThang = TenThang
            End Sub
            Public Function Thang() As Integer
                Return _Thang
            End Function
            Public Function TenThang() As String
                Return _TenThang
            End Function
            Public Overrides Function ToString() As String
                Return _TenThang
            End Function
        End Class
#End Region
#Region "Load data"
        Private Sub Nam_Load()
            Dim currentYear As Integer = Now.Year
            cbxTheoNam.Items.Clear()
            Try
                While (currentYear >= 2010)
                    cbxTheoNam.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                    cbxNamThang.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                    currentYear = currentYear - 1
                End While
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private IndexCurrentMonth As Integer = 0
        Private Sub Thang_Load()
            Dim currentYear As Integer = 1
            cbxTheoThang.Items.Clear()
            Try
                Dim i As Integer = 0
                While (currentYear <= 12)
                    cbxTheoThang.Items.Add(New MonthInfo(currentYear, currentYear.ToString))
                    If currentYear = Now.Month Then
                        IndexCurrentMonth = i
                    End If
                    currentYear = currentYear + 1
                    i = i + 1
                End While
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Private Sub dgv_KhachHang_QuickSearch_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv_KhachHang_QuickSearch.CellDoubleClick
            Try
                lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(e.RowIndex).Cells("KhachHangId_ThongKe").Value.ToString()
                txtTenKhachHang.Text = dgv_KhachHang_QuickSearch.Rows(e.RowIndex).Cells("TenKhachHang_S").Value.ToString()
                Panel_KhachHang.Visible = False
            Catch ex As Exception

            End Try
        End Sub
#End Region
        Private Sub txtTenKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyDown
            Try
                If e.KeyCode = Keys.Enter Then
                    lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                    '---------Lấy Thống Tin Khách Hàng----------
                    Dim dbConn As New OleDb.OleDbConnection
                    Dim dataAccess As New DataAccess
                    Dim dsTinhThanh As New DataSet
                    Dim dtTinhThanh As New DataTable
                    Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangId.Text
                    Dim adapter As OleDb.OleDbDataAdapter
                    dataAccess.ConnectDatabase_DBAccess(dbConn)
                    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                    adapter.Fill(dsTinhThanh)

                    dtTinhThanh = dsTinhThanh.Tables(0)
                    '
                    txtMaKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("CodeKhachHang")), "", dtTinhThanh.Rows(0)("CodeKhachHang"))
                    txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                    Panel_KhachHang.Visible = False
                    cmdThongKe.Focus()
                    dbConn.Close()
                ElseIf e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then

                End If
            Catch ex As Exception

            End Try
        End Sub
        Private Sub txtTenKhachHang_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenKhachHang.KeyUp
            Try
                If txtTenKhachHang.Text <> "" Then
                    KhachHang_Quick_Search()
                    If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                        dgv_KhachHang_QuickSearch.Focus()
                        Panel_KhachHang.Visible = True
                    End If
                Else
                    Panel_KhachHang.Visible = False
                    lblKhachHangId.Text = "0"
                    txtMaKhachHang.Text = ""
                End If
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub
        Private Sub txtTenKhachHang_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtTenKhachHang.Leave
            Try
                Panel_KhachHang.Visible = False
            Catch ex As Exception

            End Try
        End Sub
        Sub KhachHang_Quick_Search()
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dsKhachHang As New DataSet
                Dim dtKhachHang As New DataTable
                Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where TenKhachHang Like N'%" & txtTenKhachHang.Text.Trim & "%'"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dsKhachHang)
                dbConn.Close()
                dtKhachHang = dsKhachHang.Tables(0)
                Panel_KhachHang.Visible = True
                dgv_KhachHang_QuickSearch.AutoGenerateColumns = False
                dgv_KhachHang_QuickSearch.DataSource = dtKhachHang
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub
        Public Sub AutoComplete_MaKhachHang()
            Try
                'Auto Complete 
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dtCustomerInfo As New DataTable
                Dim query As String = "Select CodeKhachHang From DanhMucKhachHang Where Xoa=0"
                Dim adapter As OleDb.OleDbDataAdapter
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtCustomerInfo)
                dbConn.Close()
                aMaKhachHang = New AutoCompleteStringCollection
                For i As Integer = 0 To dtCustomerInfo.Rows.Count - 1
                    aMaKhachHang.Add(dtCustomerInfo.Rows(i)("CodeKhachHang"))
                Next
                txtMaKhachHang.AutoCompleteMode = AutoCompleteMode.SuggestAppend
                txtMaKhachHang.AutoCompleteSource = AutoCompleteSource.CustomSource
                txtMaKhachHang.AutoCompleteCustomSource = aMaKhachHang
            Catch ex As Exception

            End Try
        End Sub
        Private Sub txtMaKhachHang_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtMaKhachHang.KeyDown
            Try
                If e.KeyCode = Keys.Enter Then
                    'Truyen MaKhachHang De Lay Thong Tin
                    Dim dtKhachHangInfo As New DataTable
                    Dim dbConn As New OleDb.OleDbConnection
                    Dim dataAccess As New DataAccess
                    Dim adapter As OleDb.OleDbDataAdapter
                    Dim dbCommand As OleDb.OleDbCommand
                    dataAccess.ConnectDatabase_DBAccess(dbConn)
                    dbCommand = New OleDbCommand("spKhachHang_Item", dbConn)
                    dbCommand.CommandType = CommandType.StoredProcedure
                    dbCommand.Parameters.Add("@KhachHangId", OleDbType.Integer).Value = 0
                    dbCommand.Parameters.Add("@CodeKhachHang", OleDbType.VarWChar).Value = txtMaKhachHang.Text.Trim
                    dbCommand.Parameters.Add("@Type", OleDbType.Integer).Value = 1
                    adapter = New OleDb.OleDbDataAdapter(dbCommand)
                    adapter.Fill(dtKhachHangInfo)
                    dbConn.Close()
                    If dtKhachHangInfo.Rows.Count > 0 Then
                        lblKhachHangId.Text = dtKhachHangInfo.Rows(0)("KhachHangId")
                        txtMaKhachHang.Text = IIf(IsDBNull(dtKhachHangInfo.Rows(0)("CodeKhachHang")), "", dtKhachHangInfo.Rows(0)("CodeKhachHang"))
                        txtTenKhachHang.Text = IIf(IsDBNull(dtKhachHangInfo.Rows(0)("TenKhachHang")), "", dtKhachHangInfo.Rows(0)("TenKhachHang"))
                    Else
                        txtMaKhachHang.Focus()
                    End If
                End If
            Catch ex As Exception

            End Try
        End Sub
        Private Sub dgv_KhachHang_QuickSearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgv_KhachHang_QuickSearch.KeyDown
            Try
                Select Case e.KeyCode
                    Case Keys.Down
                        Panel_KhachHang.Visible = True
                    Case Keys.Up
                        Panel_KhachHang.Visible = True
                    Case Keys.Enter
                        lblKhachHangId.Text = dgv_KhachHang_QuickSearch.Rows(dgv_KhachHang_QuickSearch.CurrentCell.RowIndex).Cells("KhachHangId_Search").Value.ToString()
                        '---------Lấy Thống Tin Khách Hàng----------
                        Dim dbConn As New OleDb.OleDbConnection
                        Dim dataAccess As New DataAccess
                        Dim dsTinhThanh As New DataSet
                        Dim dtTinhThanh As New DataTable
                        Dim query As String = "Select CodeKhachHang,TenKhachHang From DanhMucKhachHang Where KhachHangId=" & lblKhachHangId.Text
                        Dim adapter As OleDb.OleDbDataAdapter
                        dataAccess.ConnectDatabase_DBAccess(dbConn)
                        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                        adapter.Fill(dsTinhThanh)

                        dtTinhThanh = dsTinhThanh.Tables(0)
                        '
                        txtMaKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("CodeKhachHang")), "", dtTinhThanh.Rows(0)("CodeKhachHang"))
                        txtTenKhachHang.Text = IIf(IsDBNull(dtTinhThanh.Rows(0)("TenKhachHang")), "", dtTinhThanh.Rows(0)("TenKhachHang"))
                        '
                        Panel_KhachHang.Visible = False
                        dbConn.Close()
                    Case Else
                        txtTenKhachHang.Focus()
                End Select
            Catch ex As Exception

            End Try
        End Sub

        Private Sub cemXuatExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cemXuatExcel.Click
            Try
                frmSaveFile.FileType = ".xls"
                ShowModalForm(frmSaveFile, False, False)
                Dim result As Boolean
                result = frmSaveFile.Result
                If result Then
                    Dim Path As String = frmSaveFile.Path
                    Dim FileName As String = frmSaveFile.FileName
                    ExportExcelFull(Path, FileName & ".xls")
                End If
                frmSaveFile.txtPath.Text = ""
                frmSaveFile.txtFile.Text = ""
            Catch ex As Exception

            End Try
        End Sub

        Private contentExcelXML_Thread1 As String
        Private contentExcelXML_Thread2 As String
        Private Function HeaderExcel() As String
            Try
                Dim SubTitle As String
                If rbtTheoNgay.Checked Then
                    SubTitle = "Ngày " & formatDateVN2String(dtpTheoNgay.Value)
                ElseIf rbtTheoThang.Checked Then
                    Dim Thang As MonthInfo
                    Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                    Dim Nam As YearInfo
                    Nam = CType(cbxNamThang.SelectedItem, YearInfo)
                    SubTitle = " Tháng " & Thang.TenThang & " Năm " & Nam.TenNam
                ElseIf rbtTheoNam.Checked Then
                    Dim Nam As YearInfo
                    Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                    SubTitle = " Năm " & Nam.TenNam
                ElseIf rbtTuNgay.Checked Then
                    SubTitle = " Từ ngày " & formatDateVN2String(dtpTuNgay.Value) & " Đến ngày " & formatDateVN2String(dtpDenNgay.Value)
                    'ElseIf rbtTenKhachHang.Checked Then
                    '    SubTitle = "Khách hàng " & txtTenKhachHang.Text.Trim()
                End If
                'Dim excelDoc As System.IO.File = New System.IO.StreamWriter(FileName)
                Dim startExcelXML As String
                startExcelXML = "<?xml version=""1.0""?>" & vbCrLf & _
                "<?mso-application progid=""Excel.Sheet""?>" & vbCrLf & _
                "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
                "xmlns:o =""urn:schemas-microsoft-com:office:office""" & vbCrLf & _
                "xmlns:x =""urn:schemas-microsoft-com:office:excel""" & vbCrLf & _
                "xmlns:ss =""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
                 "xmlns:html=""http://www.w3.org/TR/REC-html40""" & ">" & vbCrLf & _
                 "<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office""" & ">" & vbCrLf & _
                  "<LastAuthor>NG0C TRAM</LastAuthor>" & vbCrLf & _
                  "<Created>2011-04-04T13:47:34Z</Created>" & vbCrLf & _
                  "<Version>11.9999</Version>" & vbCrLf & _
                 "</DocumentProperties>" & vbCrLf & _
                 "<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
                  "<WindowHeight>10005</WindowHeight>" & vbCrLf & _
                  "<WindowWidth>10005</WindowWidth>" & vbCrLf & _
                  "<WindowTopX>120</WindowTopX>" & vbCrLf & _
                  "<WindowTopY>135</WindowTopY>" & vbCrLf & _
                  "<ProtectStructure>False</ProtectStructure>" & vbCrLf & _
                  "<ProtectWindows>False</ProtectWindows>" & vbCrLf & _
                 "</ExcelWorkbook>" & vbCrLf & _
                 "<Styles>" & vbCrLf & _
                  "<Style ss:ID=""Default""" & " ss:Name=""Normal""" & ">" & vbCrLf & _
                   "<Alignment ss:Vertical=""Bottom""" & "/>" & vbCrLf & _
                   "<Borders/>" & vbCrLf & _
                   "<Font/>" & vbCrLf & _
                   "<Interior/>" & vbCrLf & _
                   "<NumberFormat/>" & vbCrLf & _
                   "<Protection/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""s22""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "<Font x:Family=""Swiss""" & " ss:Size=""" & "16""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s24""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "<Font x:Family=""Swiss""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s27""" & ">" & vbCrLf & _
                   "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                   "<Interior ss:Color=""" & "#99CCFF""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s29""" & ">" & vbCrLf & _
                   "<Alignment ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                    "<Style ss:ID=""" & "s30""" & ">" & vbCrLf & _
                    "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                    "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                    "<Interior ss:Color=""" & "#CCFFCC""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                    "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s31""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s33""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s32""" & ">" & vbCrLf & _
                  "<Alignment ss:Horizontal=""" & "Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                 "</Styles>" & vbCrLf & _
                 "<Worksheet ss:Name=""" & "Sheet1""" & ">" & vbCrLf & _
                  "<Table>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "98.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "99.75""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "149.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "101.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "106.5""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "72""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "50.25""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "69""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "84.75""" & "/>" & vbCrLf & _
                   "<Row ss:AutoFitHeight=""" & "0""" & " ss:Height=""" & "24""" & ">" & vbCrLf & _
                    "<Cell ss:MergeAcross=""" & "7""" & " ss:StyleID=""" & "s22""" & "><Data ss:Type=""" & "String""" & ">BÁO CÁO THỐNG KÊ DOANH THU</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row ss:Height=""" & "20.25""" & ">" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:MergeAcross=""" & "3""" & " ss:StyleID=""" & "s24""" & "><Data ss:Type=""" & "String""" & ">" & SubTitle & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row>" & vbCrLf & _
                    "<Cell ss:Index=""" & "4""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:Index=""" & "9""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row ss:Index=""" & "6""" & ">" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TÊN KHÁCH HÀNG</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">MÃ SỐ THUẾ</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">ĐỊA CHỈ</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">NGÀY BÁN</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">SỐ HÓA ĐƠN</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">DOANH THU</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">DOANH THU CÔNG TY</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">NGUỒN DOANH THU</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                   "</Row>"
                Return startExcelXML
            Catch ex As Exception

            End Try
        End Function
        Private Function FooterExcel() As String
            Try
                Dim endExcelXML As String
                endExcelXML = "</Table>" & vbCrLf & _
                  "<WorksheetOptions xmlns=""" & "urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
                   "<Print>" & vbCrLf & _
                    "<ValidPrinterInfo/>" & vbCrLf & _
                    "<PaperSizeIndex>0</PaperSizeIndex>" & vbCrLf & _
                    "<HorizontalResolution>600</HorizontalResolution>" & vbCrLf & _
                    "<VerticalResolution>600</VerticalResolution>" & vbCrLf & _
                   "</Print>" & vbCrLf & _
                   "<Selected/>" & vbCrLf & _
                   "<Panes>" & vbCrLf & _
                    "<Pane>" & vbCrLf & _
                     "<Number>3</Number>" & vbCrLf & _
                     "<ActiveRow>1</ActiveRow>" & vbCrLf & _
                     "<ActiveCol>2</ActiveCol>" & vbCrLf & _
                     "<RangeSelection>R2C3:R2C4</RangeSelection>" & vbCrLf & _
                    "</Pane>" & vbCrLf & _
                   "</Panes>" & vbCrLf & _
                   "<ProtectObjects>False</ProtectObjects>" & vbCrLf & _
                   "<ProtectScenarios>False</ProtectScenarios>" & vbCrLf & _
                  "</WorksheetOptions>" & vbCrLf & _
                 "</Worksheet>" & vbCrLf & _
                "</Workbook>"
                Return endExcelXML
            Catch ex As Exception

            End Try
        End Function
        Sub ExportExcel_Thread1()
            Try
                For i As Integer = 1 To (dgvThongKeDoanhThu.RowsCount - 1) / 2
                    'Thread 2
                    If dgvThongKeDoanhThu(i, 0).ColumnSpan = 8 Then
                        contentExcelXML_Thread1 = contentExcelXML_Thread1 & "<Row>" & vbCrLf & _
                                                "<Cell ss:MergeAcross=""" & "7""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 0).Value & "</Data></Cell>" & vbCrLf & _
                                            "</Row>"
                    Else
                        contentExcelXML_Thread1 = contentExcelXML_Thread1 & "<Row>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & "></Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 1).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 2).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 3).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dgvThongKeDoanhThu(i, 5).Value.ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dgvThongKeDoanhThu(i, 6).Value.ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 7).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                   "</Row>"
                    End If
                Next
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub

        Sub ExportExcel_Thread2()
            Try
                For i As Integer = (dgvThongKeDoanhThu.RowsCount - 1) / 2 To dgvThongKeDoanhThu.RowsCount - 1
                    'Thread 2
                    If dgvThongKeDoanhThu(i, 0).ColumnSpan = 8 Then
                        contentExcelXML_Thread2 = contentExcelXML_Thread2 & "<Row>" & vbCrLf & _
                                                "<Cell ss:MergeAcross=""" & "7""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 0).Value & "</Data></Cell>" & vbCrLf & _
                                            "</Row>"
                    Else
                        contentExcelXML_Thread2 = contentExcelXML_Thread2 & "<Row>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & "></Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 1).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 2).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 3).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dgvThongKeDoanhThu(i, 5).Value.ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dgvThongKeDoanhThu(i, 6).Value.ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 7).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                   "</Row>"
                    End If
                Next
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub
        Sub ExportExcelFull(ByVal Path As String, ByVal FileName As String)
            Try
                Dim SubTitle As String
                If rbtTheoNgay.Checked Then
                    SubTitle = "Ngày " & formatDateVN2String(dtpTheoNgay.Value)
                ElseIf rbtTheoThang.Checked Then
                    Dim Thang As MonthInfo
                    Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                    Dim Nam As YearInfo
                    Nam = CType(cbxNamThang.SelectedItem, YearInfo)
                    SubTitle = " Tháng " & Thang.TenThang & " Năm " & Nam.TenNam
                ElseIf rbtTheoNam.Checked Then
                    Dim Nam As YearInfo
                    Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                    SubTitle = " Năm " & Nam.TenNam
                ElseIf rbtTuNgay.Checked Then
                    SubTitle = " Từ ngày " & formatDateVN2String(dtpTuNgay.Value) & " Đến ngày " & formatDateVN2String(dtpDenNgay.Value)
                    'ElseIf rbtTenKhachHang.Checked Then
                    '    SubTitle = "Khách hàng " & txtTenKhachHang.Text.Trim()
                End If
                'Dim excelDoc As System.IO.File = New System.IO.StreamWriter(FileName)
                Dim startExcelXML As String
                startExcelXML = "<?xml version=""1.0""?>" & vbCrLf & _
                "<?mso-application progid=""Excel.Sheet""?>" & vbCrLf & _
                "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
                "xmlns:o =""urn:schemas-microsoft-com:office:office""" & vbCrLf & _
                "xmlns:x =""urn:schemas-microsoft-com:office:excel""" & vbCrLf & _
                "xmlns:ss =""urn:schemas-microsoft-com:office:spreadsheet""" & vbCrLf & _
                 "xmlns:html=""http://www.w3.org/TR/REC-html40""" & ">" & vbCrLf & _
                 "<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office""" & ">" & vbCrLf & _
                  "<LastAuthor>NG0C TRAM</LastAuthor>" & vbCrLf & _
                  "<Created>2011-04-04T13:47:34Z</Created>" & vbCrLf & _
                  "<Version>11.9999</Version>" & vbCrLf & _
                 "</DocumentProperties>" & vbCrLf & _
                 "<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
                  "<WindowHeight>10005</WindowHeight>" & vbCrLf & _
                  "<WindowWidth>10005</WindowWidth>" & vbCrLf & _
                  "<WindowTopX>120</WindowTopX>" & vbCrLf & _
                  "<WindowTopY>135</WindowTopY>" & vbCrLf & _
                  "<ProtectStructure>False</ProtectStructure>" & vbCrLf & _
                  "<ProtectWindows>False</ProtectWindows>" & vbCrLf & _
                 "</ExcelWorkbook>" & vbCrLf & _
                 "<Styles>" & vbCrLf & _
                  "<Style ss:ID=""Default""" & " ss:Name=""Normal""" & ">" & vbCrLf & _
                   "<Alignment ss:Vertical=""Bottom""" & "/>" & vbCrLf & _
                   "<Borders/>" & vbCrLf & _
                   "<Font/>" & vbCrLf & _
                   "<Interior/>" & vbCrLf & _
                   "<NumberFormat/>" & vbCrLf & _
                   "<Protection/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""s22""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "<Font x:Family=""Swiss""" & " ss:Size=""" & "16""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s24""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "<Font x:Family=""Swiss""" & " ss:Color=""" & "#FF0000""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s27""" & ">" & vbCrLf & _
                   "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                   "<Interior ss:Color=""" & "#99CCFF""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s29""" & ">" & vbCrLf & _
                   "<Alignment ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                    "<Style ss:ID=""" & "s30""" & ">" & vbCrLf & _
                    "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                    "<Font x:Family=""" & "Swiss""" & " ss:Bold=""" & "1""" & "/>" & vbCrLf & _
                    "<Interior ss:Color=""" & "#CCFFCC""" & " ss:Pattern=""" & "Solid""" & "/>" & vbCrLf & _
                    "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s31""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""" & "Left""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s33""" & ">" & vbCrLf & _
                   "<Alignment ss:Horizontal=""" & "Right""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                   "</Style>" & vbCrLf & _
                  "<Style ss:ID=""" & "s32""" & ">" & vbCrLf & _
                  "<Alignment ss:Horizontal=""" & "Center""" & " ss:Vertical=""" & "Bottom""" & "/>" & vbCrLf & _
                  "</Style>" & vbCrLf & _
                 "</Styles>" & vbCrLf & _
                 "<Worksheet ss:Name=""" & "Sheet1""" & ">" & vbCrLf & _
                  "<Table>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "98.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "99.75""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "149.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "101.25""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "106.5""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "72""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "93.75""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "50.25""" & "/>" & vbCrLf & _
                   "<Column ss:Width=""" & "69""" & "/>" & vbCrLf & _
                   "<Column ss:AutoFitWidth=""" & "0""" & " ss:Width=""" & "84.75""" & "/>" & vbCrLf & _
                   "<Row ss:AutoFitHeight=""" & "0""" & " ss:Height=""" & "24""" & ">" & vbCrLf & _
                    "<Cell ss:MergeAcross=""" & "7""" & " ss:StyleID=""" & "s22""" & "><Data ss:Type=""" & "String""" & ">BÁO CÁO THỐNG KÊ SẢN LƯỢNG TIÊU THỤ KHÁCH HÀNG</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row ss:Height=""" & "20.25""" & ">" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:MergeAcross=""" & "3""" & " ss:StyleID=""" & "s24""" & "><Data ss:Type=""" & "String""" & ">" & SubTitle & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row>" & vbCrLf & _
                    "<Cell ss:Index=""" & "4""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:Index=""" & "9""" & " ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "/>" & vbCrLf & _
                   "</Row>" & vbCrLf & _
                   "<Row ss:Index=""" & "6""" & ">" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">TÊN KHÁCH HÀNG</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">MÃ SỐ THUẾ</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">ĐỊA CHỈ</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">NGÀY BÁN</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">SỐ HÓA ĐƠN</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">SẢN LƯỢNG</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">NGUỒN MUA HÀNG</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s27""" & "><Data ss:Type=""" & "String""" & ">NHÂN VIÊN TIẾP THỊ</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                   "</Row>"
                ExportToFile(Path, FileName, startExcelXML)
                Dim contentExcelXML As String = ""
                'contentExcelXML = DevideRowGrid()
                Dim EndRow As Integer = dgvThongKeDoanhThu.RowsCount - 1
                Dim StartRow As Integer = 0
                Dim K As Integer = EndRow \ 100
                Dim SoDu As Integer = EndRow Mod 100
                Dim aK(100) As Integer
                For i As Integer = 1 To 100
                    If K * i <= EndRow Then
                        aK(i) = K * i
                    End If
                Next
                aK(100) = 100 * K + SoDu
                Dim sRet As String = ""
                frmBusyLoading.Show()
                frmBusyLoading.progress.Minimum = 0
                frmBusyLoading.progress.Maximum = 100
                For i As Integer = 0 To 99
                    sRet = ConvertExcel(aK(i) + 1, aK(i + 1))
                    If sRet <> "" Then
                        ExportToFile(Path, FileName, sRet)
                    End If
                    frmBusyLoading.progress.Value = i
                Next
                frmBusyLoading.Close()
                'frmBusyLoading.Show()
                'frmBusyLoading.progress.Minimum = 0
                'frmBusyLoading.progress.Maximum = dgvThongKeDoanhThu.RowsCount - 1
                'Dim contentExcelXML As String = ""
                'For i As Integer = 1 To dgvThongKeDoanhThu.RowsCount - 1
                '    If dgvThongKeDoanhThu(i, 0).ColumnSpan = 8 Then
                '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                '                                "<Cell ss:MergeAcross=""" & "7""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 0).Value & "</Data></Cell>" & vbCrLf & _
                '                            "</Row>"
                '    Else
                '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & "></Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 1).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 2).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 3).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dgvThongKeDoanhThu(i, 5).Value.ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dgvThongKeDoanhThu(i, 6).Value.ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 7).Value & "</Data></Cell>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '   "</Row>"
                '    End If
                '    frmBusyLoading.progress.Value = i
                'Next
                'For i As Integer = 0 To dtExportExcel.Rows.Count - 1
                '    If dtExportExcel.Rows(i)("DoanhThuCty") = "" Then
                '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                '                                "<Cell ss:MergeAcross=""" & "7""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dtExportExcel.Rows(i)("TenKhachHang") & "</Data></Cell>" & vbCrLf & _
                '                            "</Row>"
                '    Else
                '        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & "></Data></Cell>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dtExportExcel.Rows(i)("MaSoThue") & "</Data></Cell>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dtExportExcel.Rows(i)("DiaChi") & "</Data></Cell>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dtExportExcel.Rows(i)("NgayBan") & "</Data></Cell>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dtExportExcel.Rows(i)("So") & "</Data></Cell>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dtExportExcel.Rows(i)("DoanhThu").ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dtExportExcel.Rows(i)("DoanhThuCty").ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dtExportExcel.Rows(i)("NguonDoanhThu") & "</Data></Cell>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '   "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                '  "</Row>"
                '    End If
                '    frmBusyLoading.progress.Value = i
                'Next
                'frmBusyLoading.Close()
                Dim endExcelXML As String
                endExcelXML = "</Table>" & vbCrLf & _
                  "<WorksheetOptions xmlns=""" & "urn:schemas-microsoft-com:office:excel""" & ">" & vbCrLf & _
                   "<Print>" & vbCrLf & _
                    "<ValidPrinterInfo/>" & vbCrLf & _
                    "<PaperSizeIndex>0</PaperSizeIndex>" & vbCrLf & _
                    "<HorizontalResolution>600</HorizontalResolution>" & vbCrLf & _
                    "<VerticalResolution>600</VerticalResolution>" & vbCrLf & _
                   "</Print>" & vbCrLf & _
                   "<Selected/>" & vbCrLf & _
                   "<Panes>" & vbCrLf & _
                    "<Pane>" & vbCrLf & _
                     "<Number>3</Number>" & vbCrLf & _
                     "<ActiveRow>1</ActiveRow>" & vbCrLf & _
                     "<ActiveCol>2</ActiveCol>" & vbCrLf & _
                     "<RangeSelection>R2C3:R2C4</RangeSelection>" & vbCrLf & _
                    "</Pane>" & vbCrLf & _
                   "</Panes>" & vbCrLf & _
                   "<ProtectObjects>False</ProtectObjects>" & vbCrLf & _
                   "<ProtectScenarios>False</ProtectScenarios>" & vbCrLf & _
                  "</WorksheetOptions>" & vbCrLf & _
                 "</Worksheet>" & vbCrLf & _
                "</Workbook>"
                ExportToFile(Path, FileName, endExcelXML)
                'System.IO.File.WriteAllText(FileName, startExcelXML & contentExcelXML & endExcelXML)
            Catch ex As Exception
                MessageBox.Show(ex.InnerException.ToString)
            End Try
        End Sub
        Private Function ConvertExcel(ByVal iBegin As Integer, ByVal iEnd As Integer) As String
            Try
                Dim contentExcelXML As String = ""
                For i As Integer = iBegin To iEnd
                    If dgvThongKeDoanhThu(i, 0).ColumnSpan = 8 Then
                        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                                                "<Cell ss:MergeAcross=""" & "7""" & " ss:StyleID=""" & "s30""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 0).Value & "</Data></Cell>" & vbCrLf & _
                                            "</Row>"
                    Else
                        contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & "></Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 1).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 2).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 3).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s32""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 4).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s33""" & "><Data ss:Type=""" & "Number""" & ">" & dgvThongKeDoanhThu(i, 5).Value.ToString.Split(",")(0).Replace(".", "") & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 6).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s31""" & "><Data ss:Type=""" & "String""" & ">" & dgvThongKeDoanhThu(i, 7).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                    "<Cell ss:StyleID=""" & "s29""" & "/>" & vbCrLf & _
                   "</Row>"
                    End If
                Next
                Return contentExcelXML
            Catch ex As Exception

            End Try
        End Function
        'Nang Cap
        Private Sub ThongKeDoanhThu_KhachHang(ByVal TuNgay As Integer, ByVal ToiNgay As Integer)
            'Try
            '    Dim TrangThai As String
            '    If rbtTatCa.Checked Then
            '        TrangThai = "Tất cả"
            '    ElseIf rbtChuaDuyet.Checked Then
            '        TrangThai = "Chưa duyệt"
            '    ElseIf rbtDaDuyet.Checked Then
            '        TrangThai = "Đã duyệt"
            '    End If
            '    groupingList = Create_GroupingList()
            '    'Lay Danh Sach Tat Ca Cac Khach Hang
            '    Dim TypeSearch As String
            '    Dim KhachHangId_Tim As Integer
            '    Dim TenKhachHang As String
            '    If txtTenKhachHang.Text = "" Then
            '        TypeSearch = "0"
            '    Else
            '        TypeSearch = "1"
            '    End If
            '    If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
            '        KhachHangId_Tim = 0
            '        TenKhachHang = txtTenKhachHang.Text
            '    Else
            '        KhachHangId_Tim = lblKhachHangId.Text
            '        TenKhachHang = txtMaKhachHang.Text
            '    End If
            '    Dim dbConn As New OleDb.OleDbConnection
            '    Dim dataAccess As New DataAccess
            '    Dim dbCommand As OleDb.OleDbCommand
            '    Dim adapter As OleDbDataAdapter
            '    dataAccess.ConnectDatabase_DBAccess(dbConn)
            '    Dim dtDanhSachKhachHang As New DataTable
            '    Dim dtDanhSachDonHangMua As New DataTable
            '    Dim dtDanhSachDonHangDaiLy As New DataTable
            '    dbCommand = New OleDbCommand("spThongKeDoanhThu_DanhSachKhachHang", dbConn)
            '    dbCommand.CommandType = CommandType.StoredProcedure
            '    dbCommand.Parameters.Add("@TrangThai", OleDbType.VarWChar).Value = TrangThai
            '    dbCommand.Parameters.Add("@BatDat", OleDbType.VarChar).Value = TuNgay
            '    dbCommand.Parameters.Add("@KetThuc", OleDbType.VarChar).Value = ToiNgay
            '    dbCommand.Parameters.Add("@TypeSearch", OleDbType.VarWChar).Value = TypeSearch
            '    dbCommand.Parameters.Add("@KhachHangId", OleDbType.Integer).Value = KhachHangId_Tim
            '    dbCommand.Parameters.Add("@TenKhachHang", OleDbType.VarWChar).Value = TenKhachHang
            '    adapter = New OleDbDataAdapter(dbCommand)
            '    adapter.Fill(dtDanhSachKhachHang)
            '    'duyet tung khach hang
            '    frmBusyLoading.Show()
            '    frmBusyLoading.progress.Minimum = 0
            '    frmBusyLoading.progress.Maximum = dtDanhSachKhachHang.Rows.Count - 1
            '    For i As Integer = 0 To dtDanhSachKhachHang.Rows.Count - 1
            '        'title row
            '        Dim titleRow As DataRow
            '        titleRow = groupingList.NewRow
            '        titleRow.Item("TenKhachHang") = IIf(dtDanhSachKhachHang.Rows(i)("TenKhachHang") = "", dtDanhSachKhachHang.Rows(i)("TenNguoiMua"), dtDanhSachKhachHang.Rows(i)("TenKhachHang"))
            '        titleRow.Item("MaSoThue") = ""
            '        titleRow.Item("DiaChi") = ""
            '        titleRow.Item("NgayBan") = ""
            '        titleRow.Item("So") = ""
            '        titleRow.Item("DoanhThu") = ""
            '        titleRow.Item("DoanhThuCTy") = ""
            '        titleRow.Item("NguonDoanhThu") = ""
            '        titleRow.Item("NhanVienTiepThi") = ""
            '        groupingList.Rows.Add(titleRow)
            '        'detail row Don Hang Mua
            '        dtDanhSachDonHangMua = New DataTable
            '        dbCommand = New OleDbCommand("spThongKeDoanhThu_DanhSachDonHang_Mua", dbConn)
            '        dbCommand.CommandType = CommandType.StoredProcedure
            '        dbCommand.Parameters.Add("@TrangThai", OleDbType.VarWChar).Value = TrangThai
            '        dbCommand.Parameters.Add("@KhachHangId", OleDbType.Integer).Value = dtDanhSachKhachHang.Rows(i)("KhachHangId")
            '        dbCommand.Parameters.Add("@BatDat", OleDbType.VarChar).Value = TuNgay
            '        dbCommand.Parameters.Add("@KetThuc", OleDbType.VarChar).Value = ToiNgay
            '        dbCommand.Parameters.Add("@TenNguoiMua", OleDbType.VarWChar).Value = IIf(dtDanhSachKhachHang.Rows(i)("TenKhachHang") = "", dtDanhSachKhachHang.Rows(i)("TenNguoiMua"), dtDanhSachKhachHang.Rows(i)("TenKhachHang"))
            '        Dim Add As String = dtDanhSachKhachHang.Rows(i)("DiaChi")
            '        dbCommand.Parameters.Add("@DiaChi", OleDbType.VarWChar).Value = dtDanhSachKhachHang.Rows(i)("DiaChi")
            '        adapter = New OleDbDataAdapter(dbCommand)
            '        adapter.Fill(dtDanhSachDonHangMua)
            '        dtDanhSachDonHangMua.Columns.Add("DoanhThu")
            '        dtDanhSachDonHangMua.Columns.Add("DoanhThuCTy")
            '        Dim DoanhThu As Single = 0
            '        Dim DoanhThuCTy As Single = 0
            '        Dim sThanhTien As String
            '        For j As Integer = 0 To dtDanhSachDonHangMua.Rows.Count - 1
            '            'Tính Doanh Thu Khách Hàng
            '            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtDanhSachDonHangMua.Rows(j)("IdDonHangBan"))
            '            'Tính doanh Thu Cty
            '            DoanhThuCTy = DoanhThu - TinhDoanhThuCongTyTheoDonHang(dtDanhSachDonHangMua.Rows(j)("IdDonHangBan"))
            '            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '            dtDanhSachDonHangMua.Rows(j)("DoanhThu") = sThanhTien
            '            sThanhTien = CDec(DoanhThuCTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '            dtDanhSachDonHangMua.Rows(j)("DoanhThuCTy") = sThanhTien
            '            formatDate2VN(dtDanhSachDonHangMua.Rows(j)("NgayBan"))
            '            'detail row
            '            Dim detailRow As DataRow
            '            detailRow = groupingList.NewRow
            '            detailRow.Item("TenKhachHang") = ""
            '            detailRow.Item("MaSoThue") = dtDanhSachDonHangMua.Rows(j)("MaSoThue")
            '            detailRow.Item("DiaChi") = dtDanhSachDonHangMua.Rows(j)("DiaChi")
            '            detailRow.Item("NgayBan") = dtDanhSachDonHangMua.Rows(j)("NgayBan")
            '            detailRow.Item("So") = dtDanhSachDonHangMua.Rows(j)("So")
            '            detailRow.Item("DoanhThu") = dtDanhSachDonHangMua.Rows(j)("DoanhThu")
            '            detailRow.Item("DoanhThuCTy") = dtDanhSachDonHangMua.Rows(j)("DoanhThuCTy")
            '            If dtDanhSachDonHangMua.Rows(j)("HinhThucTiepThi") = "Đại lý" Then
            '                dbCommand = New OleDbCommand("spThongTinTiepThi", dbConn)
            '                dbCommand.CommandType = CommandType.StoredProcedure
            '                dbCommand.Parameters.Add("@IDDonHangBan", OleDbType.Integer).Value = Integer.Parse(dtDanhSachDonHangMua.Rows(j)("IdDonHangBan"))
            '                detailRow.Item("NguonDoanhThu") = "Mua hàng qua đại lý " & dbCommand.ExecuteScalar().ToString()
            '                detailRow.Item("NhanVienTiepThi") = ""
            '            Else
            '                detailRow.Item("NguonDoanhThu") = "Mua hàng trực tiếp"
            '                detailRow.Item("NhanVienTiepThi") = IIf(dtDanhSachDonHangMua.Rows(j)("NhanVienTiepThi") = "Chưa có", "", dtDanhSachDonHangMua.Rows(j)("NhanVienTiepThi"))
            '            End If
            '            groupingList.Rows.Add(detailRow)
            '        Next
            '        'detail row DonHang Lam Dai Ly
            '        dtDanhSachDonHangDaiLy = New DataTable
            '        dbCommand = New OleDbCommand("spThongKeDoanhThu_DanhSachDonHang_DaiLy", dbConn)
            '        dbCommand.CommandType = CommandType.StoredProcedure
            '        dbCommand.Parameters.Add("@TrangThai", OleDbType.VarWChar).Value = TrangThai
            '        dbCommand.Parameters.Add("@KhachHangId", OleDbType.Integer).Value = dtDanhSachKhachHang.Rows(i)("KhachHangId")
            '        dbCommand.Parameters.Add("@BatDat", OleDbType.VarChar).Value = TuNgay
            '        dbCommand.Parameters.Add("@KetThuc", OleDbType.VarChar).Value = ToiNgay
            '        adapter = New OleDbDataAdapter(dbCommand)
            '        adapter.Fill(dtDanhSachDonHangDaiLy)
            '        dtDanhSachDonHangDaiLy.Columns.Add("DoanhThu")
            '        dtDanhSachDonHangDaiLy.Columns.Add("DoanhThuCTy")
            '        sThanhTien = ""
            '        For j As Integer = 0 To dtDanhSachDonHangDaiLy.Rows.Count - 1
            '            'Tính Doanh Thu Khách Hàng
            '            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtDanhSachDonHangDaiLy.Rows(j)("IdDonHangBan"))
            '            'Tính doanh Thu Cty
            '            DoanhThuCTy = DoanhThu - TinhDoanhThuCongTyTheoDonHang(dtDanhSachDonHangDaiLy.Rows(j)("IdDonHangBan"))
            '            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '            dtDanhSachDonHangDaiLy.Rows(j)("DoanhThu") = sThanhTien
            '            sThanhTien = CDec(DoanhThuCTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '            dtDanhSachDonHangDaiLy.Rows(j)("DoanhThuCTy") = sThanhTien
            '            formatDate2VN(dtDanhSachDonHangDaiLy.Rows(j)("NgayBan"))
            '            'detail row
            '            Dim detailRow As DataRow
            '            detailRow = groupingList.NewRow
            '            detailRow.Item("TenKhachHang") = ""
            '            detailRow.Item("MaSoThue") = dtDanhSachDonHangDaiLy.Rows(j)("MaSoThue")
            '            detailRow.Item("DiaChi") = dtDanhSachDonHangDaiLy.Rows(j)("DiaChi")
            '            detailRow.Item("NgayBan") = dtDanhSachDonHangDaiLy.Rows(j)("NgayBan")
            '            detailRow.Item("So") = dtDanhSachDonHangDaiLy.Rows(j)("So")
            '            detailRow.Item("DoanhThu") = dtDanhSachDonHangDaiLy.Rows(j)("DoanhThu")
            '            detailRow.Item("DoanhThuCTy") = dtDanhSachDonHangDaiLy.Rows(j)("DoanhThuCTy")
            '            detailRow.Item("NguonDoanhThu") = "Đại lý tiếp thị cho " & IIf(dtDanhSachDonHangDaiLy.Rows(j)("TiepThiChoKhachHang") = "", dtDanhSachDonHangDaiLy.Rows(j)("TenNguoiMua"), dtDanhSachDonHangDaiLy.Rows(j)("TiepThiChoKhachHang"))
            '            groupingList.Rows.Add(detailRow)
            '        Next
            '        Dim SumRow As DataRow
            '        Dim sTongDoanhThuKhachHang As Long
            '        Dim sDoanhThuCongTy As Long
            '        dbCommand = New OleDbCommand("spThongKeDoanhThu_KhachHang", dbConn)
            '        dbCommand.CommandType = CommandType.StoredProcedure
            '        Dim KhachHangId As Integer = dtDanhSachKhachHang.Rows(i)("KhachHangId")
            '        dbCommand.Parameters.Add("@KhachHangId", OleDbType.Integer).Value = dtDanhSachKhachHang.Rows(i)("KhachHangId")
            '        dbCommand.Parameters.Add("@TrangThai", OleDbType.VarWChar).Value = TrangThai
            '        dbCommand.Parameters.Add("@LoaiPhi", OleDbType.VarWChar).Value = "HangHoa"
            '        dbCommand.Parameters.Add("@BatDat", OleDbType.VarChar).Value = TuNgay
            '        dbCommand.Parameters.Add("@KetThuc", OleDbType.VarChar).Value = ToiNgay
            '        dbCommand.Parameters.Add("@TenNguoiMua", OleDbType.VarWChar).Value = IIf(dtDanhSachKhachHang.Rows(i)("TenKhachHang") = "", dtDanhSachKhachHang.Rows(i)("TenNguoiMua"), dtDanhSachKhachHang.Rows(i)("TenKhachHang"))
            '        dbCommand.Parameters.Add("@DiaChi", OleDbType.VarWChar).Value = dtDanhSachKhachHang.Rows(i)("DiaChi")
            '        Try
            '            sTongDoanhThuKhachHang = dbCommand.ExecuteScalar()
            '        Catch ex As Exception
            '            MessageBox.Show("Đơn hàng của khách hàng : " & IIf(dtDanhSachKhachHang.Rows(i)("TenKhachHang") = "", dtDanhSachKhachHang.Rows(i)("TenNguoiMua"), dtDanhSachKhachHang.Rows(i)("TenKhachHang")) & " thông chưa hợp lệ.Xin vui lòng kiểm tra lại", "Thông Báo", MessageBoxButtons.OK)
            '        End Try
            '        dbCommand = New OleDbCommand("spThongKeDoanhThu_KhachHang", dbConn)
            '        dbCommand.CommandType = CommandType.StoredProcedure
            '        dbCommand.Parameters.Add("@KhachHangId", OleDbType.Integer).Value = dtDanhSachKhachHang.Rows(i)("KhachHangId")
            '        dbCommand.Parameters.Add("@TrangThai", OleDbType.VarWChar).Value = TrangThai
            '        dbCommand.Parameters.Add("@LoaiPhi", OleDbType.VarWChar).Value = "GiamTru"
            '        dbCommand.Parameters.Add("@BatDat", OleDbType.VarChar).Value = TuNgay
            '        dbCommand.Parameters.Add("@KetThuc", OleDbType.VarChar).Value = ToiNgay
            '        dbCommand.Parameters.Add("@TenNguoiMua", OleDbType.VarWChar).Value = IIf(dtDanhSachKhachHang.Rows(i)("TenKhachHang") = "", dtDanhSachKhachHang.Rows(i)("TenNguoiMua"), dtDanhSachKhachHang.Rows(i)("TenKhachHang"))
            '        dbCommand.Parameters.Add("@DiaChi", OleDbType.VarWChar).Value = dtDanhSachKhachHang.Rows(i)("DiaChi")
            '        sDoanhThuCongTy = sTongDoanhThuKhachHang - dbCommand.ExecuteScalar()
            '        '-----------------------------------
            '        SumRow = groupingList.NewRow
            '        SumRow.Item("TenKhachHang") = ""
            '        SumRow.Item("MaSoThue") = ""
            '        SumRow.Item("DiaChi") = ""
            '        SumRow.Item("NgayBan") = ""
            '        SumRow.Item("So") = "Tổng :"
            '        SumRow.Item("DoanhThu") = CDec(sTongDoanhThuKhachHang.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '        SumRow.Item("DoanhThuCTy") = CDec(sDoanhThuCongTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            '        SumRow.Item("NguonDoanhThu") = ""
            '        groupingList.Rows.Add(SumRow)
            '        frmBusyLoading.progress.Value = i
            '    Next
            '    'het duyet tung khach hang
            '    frmBusyLoading.Close()
            '    dtExportExcel = Create_GroupingList()
            '    dtExportExcel = groupingList
            '    dbConn.Close()
            '    'bind data
            '    dgvThongKeDoanhThu.Rows.Clear()
            '    dgvThongKeDoanhThu.ColumnsCount = 9
            '    'tạo header 
            '    dgvThongKeDoanhThu.FixedRows = 1
            '    dgvThongKeDoanhThu.Rows.Insert(0)
            '    dgvThongKeDoanhThu(0, 0) = New SourceGrid.Cells.ColumnHeader("Tên khách hàng")
            '    dgvThongKeDoanhThu(0, 0).Column.Width = 110
            '    dgvThongKeDoanhThu(0, 1) = New SourceGrid.Cells.ColumnHeader("Mã số thuế")
            '    dgvThongKeDoanhThu(0, 1).Column.Width = 100
            '    dgvThongKeDoanhThu(0, 2) = New SourceGrid.Cells.ColumnHeader("Địa chỉ")
            '    dgvThongKeDoanhThu(0, 2).Column.Width = 270
            '    dgvThongKeDoanhThu(0, 3) = New SourceGrid.Cells.ColumnHeader("Ngày bán")
            '    dgvThongKeDoanhThu(0, 3).Column.Width = 70
            '    dgvThongKeDoanhThu(0, 4) = New SourceGrid.Cells.ColumnHeader("Số đơn hàng")
            '    dgvThongKeDoanhThu(0, 4).Column.Width = 80
            '    dgvThongKeDoanhThu(0, 5) = New SourceGrid.Cells.ColumnHeader("Doanh thu")
            '    dgvThongKeDoanhThu(0, 5).Column.Width = 100
            '    dgvThongKeDoanhThu(0, 6) = New SourceGrid.Cells.ColumnHeader("Doanh thu công ty")
            '    dgvThongKeDoanhThu(0, 6).Column.Width = 100
            '    dgvThongKeDoanhThu(0, 7) = New SourceGrid.Cells.ColumnHeader("Nguồn doanh thu")
            '    dgvThongKeDoanhThu(0, 7).Column.Width = 250
            '    dgvThongKeDoanhThu(0, 8) = New SourceGrid.Cells.ColumnHeader("Nhân viên tiếp thị")
            '    dgvThongKeDoanhThu(0, 8).Column.Width = 150
            '    'tạo detail
            '    Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
            '    rowCOLSPAN.BackColor = Color.FromArgb(192, 255, 192)
            '    Dim StyleLeft As New SourceGrid.Cells.Views.Cell
            '    StyleLeft.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleLeft
            '    Dim StyleRight As New SourceGrid.Cells.Views.Cell
            '    StyleRight.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
            '    groupingList.DefaultView.Sort = "NgayBan ASC"
            '    For i As Integer = 0 To groupingList.Rows.Count - 1
            '        dgvThongKeDoanhThu.Rows.Insert(i + 1)
            '        If groupingList.Rows(i)("TenKhachHang").ToString <> "" Then
            '            dgvThongKeDoanhThu(i + 1, 0) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("TenKhachHang").ToString)
            '            dgvThongKeDoanhThu(i + 1, 0).ColumnSpan = 9
            '            dgvThongKeDoanhThu(i + 1, 0).View = rowCOLSPAN
            '        Else
            '            dgvThongKeDoanhThu(i + 1, 0) = New SourceGrid.Cells.Cell()
            '            dgvThongKeDoanhThu(i + 1, 1) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("MaSoThue").ToString)
            '            dgvThongKeDoanhThu(i + 1, 1).View = StyleLeft
            '            dgvThongKeDoanhThu(i + 1, 2) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("DiaChi").ToString)
            '            dgvThongKeDoanhThu(i + 1, 2).View = StyleLeft
            '            dgvThongKeDoanhThu(i + 1, 3) = New SourceGrid.Cells.Cell(ConvertVietnamese(groupingList.Rows(i)("NgayBan")))
            '            dgvThongKeDoanhThu(i + 1, 3).View = StyleRight
            '            dgvThongKeDoanhThu(i + 1, 4) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("So").ToString)
            '            dgvThongKeDoanhThu(i + 1, 4).View = StyleRight
            '            dgvThongKeDoanhThu(i + 1, 5) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("DoanhThu").ToString)
            '            dgvThongKeDoanhThu(i + 1, 5).View = StyleRight
            '            dgvThongKeDoanhThu(i + 1, 6) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("DoanhThuCTy").ToString)
            '            dgvThongKeDoanhThu(i + 1, 6).View = StyleRight
            '            dgvThongKeDoanhThu(i + 1, 7) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("NguonDoanhThu").ToString)
            '            dgvThongKeDoanhThu(i + 1, 7).View = StyleLeft
            '            dgvThongKeDoanhThu(i + 1, 8) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("NhanVienTiepThi").ToString)
            '            dgvThongKeDoanhThu(i + 1, 8).View = StyleLeft
            '        End If
            '    Next
            'Catch ex As Exception

            'End Try
        End Sub
        Function ConvertVietnamese(ByVal Ngay As String) As String
            Try
                If Ngay = "" Then
                    Return ""
                Else
                    Dim aNgay() As String = Ngay.Split("/")
                    If Integer.Parse(aNgay(0)) <= 9 Then
                        aNgay(0) = "0" & aNgay(0)
                    End If
                    If Integer.Parse(aNgay(1)) <= 9 Then
                        aNgay(1) = "0" & aNgay(1)
                    End If
                    Return aNgay(0) & "/" & aNgay(1) & "/" & aNgay(2)
                End If
            Catch ex As Exception

            End Try
        End Function
        Private Sub txtMaKhachHang_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtMaKhachHang.KeyUp
            Try
                If txtMaKhachHang.Text = "" Then
                    lblKhachHangId.Text = "0"
                    txtMaKhachHang.Text = ""
                    txtTenKhachHang.Text = ""
                End If
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
        End Sub

        Private Sub cmdTinhThanh_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdTinhThanh.CheckedChanged
            Try
                cbTinhThanh.Enabled = cmdTinhThanh.Checked
            Catch ex As Exception

            End Try
        End Sub
#Region "Nang Cap Len Phien Ban Su Dung SQLClient Provider"
        Private Sub ThongKeDoanhThu(ByVal TuNgay As Integer, ByVal ToiNgay As Integer)
            Try
                Dim TrangThai As String
                If rbtTatCa.Checked Then
                    TrangThai = "Tất cả"
                ElseIf rbtChuaDuyet.Checked Then
                    TrangThai = "Chưa duyệt"
                ElseIf rbtDaDuyet.Checked Then
                    TrangThai = "Đã duyệt"
                End If
                'Lay Danh Sach Tat Ca Cac Khach Hang
                Dim TypeSearch As String
                Dim KhachHangId_Tim As Integer
                Dim TenKhachHang As String
                If txtTenKhachHang.Text = "" Then
                    TypeSearch = "0"
                Else
                    TypeSearch = "1"
                End If
                If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
                    KhachHangId_Tim = 0
                    TenKhachHang = txtTenKhachHang.Text
                Else
                    KhachHangId_Tim = lblKhachHangId.Text
                    TenKhachHang = txtMaKhachHang.Text
                End If
                Dim dtDanhSachKhachHang As List(Of TKDT_DanhSachKhachHang)
                Dim controller As New ThongKeDoanhThuBLL
                dtDanhSachKhachHang = controller.ThongKeDoanhThu_DanhSachKhachHang(TrangThai, TuNgay, ToiNgay, TypeSearch, KhachHangId_Tim, TenKhachHang)
                'duyet tung khach hang
                If cmdTinhThanh.Checked Then
                    Dim dtDanhSachKhachHang_TinhThanh As New List(Of TKDT_DanhSachKhachHang)
                    dtDanhSachKhachHang_TinhThanh = dtDanhSachKhachHang.FindAll(AddressOf findByMaTinhThanh)
                    ThongKe(dtDanhSachKhachHang_TinhThanh, TuNgay, ToiNgay)
                Else
                    ThongKe(dtDanhSachKhachHang, TuNgay, ToiNgay)
                End If

            Catch ex As Exception

            End Try
        End Sub
        Function findByMaTinhThanh(ByVal b As TKDT_DanhSachKhachHang) As Boolean
            Try
                If b.MaTinhThanh = cbTinhThanh.SelectedValue Then
                    Return True
                Else
                    Return False
                End If
            Catch ex As Exception

            End Try
        End Function
        Protected Sub ThongKe(ByVal dtDanhSachKhachHang As List(Of TKDT_DanhSachKhachHang), ByVal TuNgay As Integer, ByVal ToiNgay As Integer)
            Try
                Dim TrangThai As String
                If rbtTatCa.Checked Then
                    TrangThai = "Tất cả"
                ElseIf rbtChuaDuyet.Checked Then
                    TrangThai = "Chưa duyệt"
                ElseIf rbtDaDuyet.Checked Then
                    TrangThai = "Đã duyệt"
                End If
                groupingList = Create_GroupingList()
                'Lay Danh Sach Tat Ca Cac Khach Hang
                Dim TypeSearch As String
                Dim KhachHangId_Tim As Integer
                Dim TenKhachHang As String
                If txtTenKhachHang.Text = "" Then
                    TypeSearch = "0"
                Else
                    TypeSearch = "1"
                End If
                If lblKhachHangId.Text = "0" Or lblKhachHangId.Text = "" Then
                    KhachHangId_Tim = 0
                    TenKhachHang = txtTenKhachHang.Text
                Else
                    KhachHangId_Tim = lblKhachHangId.Text
                    TenKhachHang = txtMaKhachHang.Text
                End If
                ''
                Dim controller As New ThongKeDoanhThuBLL
                frmBusyLoading.Show()
                frmBusyLoading.progress.Minimum = 0
                frmBusyLoading.progress.Maximum = dtDanhSachKhachHang.Count - 1
                Dim TenNguoiMua As String
                Dim TongDoanhThu As Single = 0
                Dim TongDoanhThuCongTy As Single = 0
                Dim sTongSanLuongKhachHang As Long = 0
                Dim cRow As Integer = 0
                For i As Integer = 0 To dtDanhSachKhachHang.Count - 1
                    'title row
                    cRow = 0
                    Dim titleRow As DataRow
                    TenNguoiMua = IIf(dtDanhSachKhachHang(i).TenKhachHang = "", dtDanhSachKhachHang(i).TenNguoiMua, dtDanhSachKhachHang(i).TenKhachHang)
                    titleRow = groupingList.NewRow
                    titleRow.Item("TenKhachHang") = TenNguoiMua
                    titleRow.Item("MaSoThue") = ""
                    titleRow.Item("DiaChi") = ""
                    titleRow.Item("NgayBan") = ""
                    titleRow.Item("So") = ""
                    titleRow.Item("SanLuong") = ""
                    titleRow.Item("NguonDoanhThu") = ""
                    titleRow.Item("NhanVienTiepThi") = ""
                    titleRow.Item("IDDonHangBan") = ""
                    groupingList.Rows.Add(titleRow)
                    cRow = 1
                    'detail row Don Hang Mua
                    Dim dtDanhSachDonHangMua As New List(Of TKDT_DanhSachDonHang_Mua)
                    dtDanhSachDonHangMua = controller.ThongKeDoanhThu_DanhSachDonHang_Mua(TrangThai, dtDanhSachKhachHang(i).KhachHangID, TuNgay, ToiNgay, TenNguoiMua, dtDanhSachKhachHang(i).DiaChi)
                    Dim SanLuong As Single = 0
                    Dim sSanLuong As String
                    sTongSanLuongKhachHang = 0
                    For j As Integer = 0 To dtDanhSachDonHangMua.Count - 1
                        If dtDanhSachDonHangMua(j).HinhThucTiepThi <> "Đại lý" Then
                            cRow = cRow + 1
                            'Tính Doanh Thu Khách Hàng
                            SanLuong = TinhSanLuongKhachHangTheoDonHang(dtDanhSachDonHangMua(j).IDDonHangBan)
                            sTongSanLuongKhachHang = sTongSanLuongKhachHang + SanLuong
                            sSanLuong = CDec(SanLuong.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                            dtDanhSachDonHangMua(j).DoanhThu = sSanLuong
                            formatDate2VN(dtDanhSachDonHangMua(j).NgayBan)
                            'detail row
                            Dim detailRow As DataRow
                            detailRow = groupingList.NewRow
                            detailRow.Item("TenKhachHang") = ""
                            detailRow.Item("MaSoThue") = dtDanhSachDonHangMua(j).MaSoThue
                            detailRow.Item("DiaChi") = dtDanhSachDonHangMua(j).DiaChi
                            detailRow.Item("NgayBan") = dtDanhSachDonHangMua(j).NgayBan
                            detailRow.Item("So") = dtDanhSachDonHangMua(j).So
                            detailRow.Item("SanLuong") = dtDanhSachDonHangMua(j).DoanhThu
                            If dtDanhSachDonHangMua(j).HinhThucTiepThi = "Đại lý" Then
                                detailRow.Item("NguonDoanhThu") = "Mua hàng qua đại lý " & controller.ThongTinTiepThi(dtDanhSachDonHangMua(j).IDDonHangBan)
                                detailRow.Item("NhanVienTiepThi") = ""
                            Else
                                detailRow.Item("NguonDoanhThu") = "Mua hàng trực tiếp"
                                detailRow.Item("NhanVienTiepThi") = IIf(dtDanhSachDonHangMua(j).NhanVienTiepThi = "Chưa có", "", dtDanhSachDonHangMua(j).NhanVienTiepThi)
                            End If
                            detailRow.Item("IDDonHangBan") = dtDanhSachDonHangMua(j).IDDonHangBan
                            groupingList.Rows.Add(detailRow)
                        End If
                    Next
                    'detail row DonHang Lam Dai Ly
                    Dim dtDanhSachDonHangDaiLy As New List(Of TKDT_DanhSachDonHang_DaiLy)
                    dtDanhSachDonHangDaiLy = controller.ThongKeDoanhThu_DanhSachDonHang_DaiLy(TrangThai, dtDanhSachKhachHang(i).KhachHangID, TuNgay, ToiNgay)
                    sSanLuong = ""
                    For j As Integer = 0 To dtDanhSachDonHangDaiLy.Count - 1
                        cRow = cRow + 1
                        'Tính Doanh Thu Khách Hàng
                        SanLuong = TinhSanLuongKhachHangTheoDonHang(dtDanhSachDonHangDaiLy(j).IDDonHangBan)
                        sTongSanLuongKhachHang = sTongSanLuongKhachHang + SanLuong
                        'fixed 07/11/2011 khong tinh cho truong hop lam dai ly
                        'TongDoanhThu = TongDoanhThu + DoanhThu
                        'Tính doanh Thu Cty
                        'DoanhThuCTy = DoanhThu - TinhDoanhThuCongTyTheoDonHang(dtDanhSachDonHangDaiLy(j).IDDonHangBan)
                        'fixed 07/11/2011 khong tinh cho truong hop lam dai ly
                        'TongDoanhThuCongTy = TongDoanhThuCongTy + DoanhThuCTy
                        sSanLuong = CDec(SanLuong.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                        dtDanhSachDonHangDaiLy(j).DoanhThu = sSanLuong
                        'sThanhTien = CDec(DoanhThuCTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                        'dtDanhSachDonHangDaiLy(j).DoanhThuCTy = sThanhTien
                        formatDate2VN(dtDanhSachDonHangDaiLy(j).NgayBan)
                        'detail row
                        Dim detailRow As DataRow
                        detailRow = groupingList.NewRow
                        detailRow.Item("TenKhachHang") = ""
                        detailRow.Item("MaSoThue") = dtDanhSachDonHangDaiLy(j).MaSoThue
                        detailRow.Item("DiaChi") = dtDanhSachDonHangDaiLy(j).DiaChi
                        detailRow.Item("NgayBan") = dtDanhSachDonHangDaiLy(j).NgayBan
                        detailRow.Item("So") = dtDanhSachDonHangDaiLy(j).So
                        detailRow.Item("SanLuong") = dtDanhSachDonHangDaiLy(j).DoanhThu
                        'detailRow.Item("DoanhThuCTy") = dtDanhSachDonHangDaiLy(j).DoanhThuCTy
                        detailRow.Item("NguonDoanhThu") = "Đại lý tiếp thị cho " & IIf(dtDanhSachDonHangDaiLy(j).TiepThiChoKhachHang = "", dtDanhSachDonHangDaiLy(j).TenNguoiMua, dtDanhSachDonHangDaiLy(j).TiepThiChoKhachHang)
                        detailRow.Item("IDDonHangBan") = dtDanhSachDonHangDaiLy(j).IDDonHangBan
                        groupingList.Rows.Add(detailRow)
                    Next
                    Dim SumRow As DataRow
                    'Dim sDoanhThuCongTy As Long
                    'sTongSanLuongKhachHang = controller.ThongKeSanLuong_KhachHang(dtDanhSachKhachHang(i).KhachHangID, TrangThai, "HangHoa", TuNgay, ToiNgay, IIf(dtDanhSachKhachHang(i).TenKhachHang = "", dtDanhSachKhachHang(i).TenNguoiMua, dtDanhSachKhachHang(i).TenKhachHang), dtDanhSachKhachHang(i).DiaChi)
                    'sDoanhThuCongTy = sTongDoanhThuKhachHang - controller.ThongKeDoanhThu_KhachHang(dtDanhSachKhachHang(i).KhachHangID, TrangThai, "GiamTru", TuNgay, ToiNgay, IIf(dtDanhSachKhachHang(i).TenKhachHang = "", dtDanhSachKhachHang(i).TenNguoiMua, dtDanhSachKhachHang(i).TenKhachHang), dtDanhSachKhachHang(i).DiaChi)
                    SumRow = groupingList.NewRow
                    SumRow.Item("TenKhachHang") = ""
                    SumRow.Item("MaSoThue") = ""
                    SumRow.Item("DiaChi") = ""
                    SumRow.Item("NgayBan") = ""
                    SumRow.Item("So") = "Tổng :"
                    SumRow.Item("SanLuong") = CDec(sTongSanLuongKhachHang.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                    'SumRow.Item("DoanhThuCTy") = CDec(sDoanhThuCongTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                    SumRow.Item("NguonDoanhThu") = ""
                    SumRow.Item("IDDonHangBan") = ""
                    If cRow <> 1 Then
                        groupingList.Rows.Add(SumRow)
                    Else
                        groupingList.Rows.Remove(titleRow)
                    End If
                    frmBusyLoading.progress.Value = i
                Next
                ''het duyet tung khach hang
                'lblTongDoanhSo.Text = CDec(TongDoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                'lblTongDoanSoCTy.Text = CDec(TongDoanhThuCongTy.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
                frmBusyLoading.Close()
                dtExportExcel = Create_GroupingList()
                dtExportExcel = groupingList
                'bind data
                dgvThongKeDoanhThu.Rows.Clear()
                dgvThongKeDoanhThu.ColumnsCount = 9
                'tạo header 
                dgvThongKeDoanhThu.FixedRows = 1
                dgvThongKeDoanhThu.Rows.Insert(0)
                dgvThongKeDoanhThu(0, 0) = New SourceGrid.Cells.ColumnHeader("Tên khách hàng")
                dgvThongKeDoanhThu(0, 0).Column.Width = 110
                dgvThongKeDoanhThu(0, 1) = New SourceGrid.Cells.ColumnHeader("Mã số thuế")
                dgvThongKeDoanhThu(0, 1).Column.Width = 100
                dgvThongKeDoanhThu(0, 2) = New SourceGrid.Cells.ColumnHeader("Địa chỉ")
                dgvThongKeDoanhThu(0, 2).Column.Width = 270
                dgvThongKeDoanhThu(0, 3) = New SourceGrid.Cells.ColumnHeader("Ngày bán")
                dgvThongKeDoanhThu(0, 3).Column.Width = 70
                dgvThongKeDoanhThu(0, 4) = New SourceGrid.Cells.ColumnHeader("Số đơn hàng")
                dgvThongKeDoanhThu(0, 4).Column.Width = 80
                dgvThongKeDoanhThu(0, 5) = New SourceGrid.Cells.ColumnHeader("Số lượng")
                dgvThongKeDoanhThu(0, 5).Column.Width = 100
                'dgvThongKeDoanhThu(0, 6) = New SourceGrid.Cells.ColumnHeader("Doanh số công ty")
                'dgvThongKeDoanhThu(0, 6).Column.Width = 100
                dgvThongKeDoanhThu(0, 6) = New SourceGrid.Cells.ColumnHeader("Nguồn mua hàng")
                dgvThongKeDoanhThu(0, 6).Column.Width = 250
                dgvThongKeDoanhThu(0, 7) = New SourceGrid.Cells.ColumnHeader("Nhân viên tiếp thị")
                dgvThongKeDoanhThu(0, 7).Column.Width = 150
                'tạo detail
                Dim rowCOLSPAN As New SourceGrid.Cells.Views.Cell
                rowCOLSPAN.BackColor = Color.FromArgb(192, 255, 192)
                Dim StyleLeft As New SourceGrid.Cells.Views.Cell
                StyleLeft.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleLeft
                Dim StyleRight As New SourceGrid.Cells.Views.Cell
                StyleRight.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
                Dim newFont As New Font(dgvThongKeDoanhThu.Font, FontStyle.Underline)
                Dim StyleLink As New SourceGrid.Cells.Views.Cell
                StyleLink.TextAlignment = DevAge.Drawing.ContentAlignment.MiddleRight
                StyleLink.Font = newFont
                StyleLink.ForeColor = Color.Blue
                groupingList.DefaultView.Sort = "NgayBan ASC"
                Dim eventController As New CellEvent
                For i As Integer = 0 To groupingList.Rows.Count - 1
                    dgvThongKeDoanhThu.Rows.Insert(i + 1)
                    If groupingList.Rows(i)("TenKhachHang").ToString <> "" Then
                        dgvThongKeDoanhThu(i + 1, 0) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("TenKhachHang").ToString)
                        dgvThongKeDoanhThu(i + 1, 0).ColumnSpan = 8
                        dgvThongKeDoanhThu(i + 1, 0).View = rowCOLSPAN
                    Else
                        dgvThongKeDoanhThu(i + 1, 0) = New SourceGrid.Cells.Cell()
                        dgvThongKeDoanhThu(i + 1, 1) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("MaSoThue").ToString)
                        dgvThongKeDoanhThu(i + 1, 1).View = StyleLeft
                        dgvThongKeDoanhThu(i + 1, 2) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("DiaChi").ToString)
                        dgvThongKeDoanhThu(i + 1, 2).View = StyleLeft
                        dgvThongKeDoanhThu(i + 1, 3) = New SourceGrid.Cells.Cell(ConvertVietnamese(groupingList.Rows(i)("NgayBan")))
                        dgvThongKeDoanhThu(i + 1, 3).View = StyleRight
                        If groupingList.Rows(i)("So").ToString = "Tổng :" Then
                            dgvThongKeDoanhThu(i + 1, 4) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("So").ToString)
                            dgvThongKeDoanhThu(i + 1, 4).View = StyleRight

                        Else
                            'Truyen them tham so nam cho so hoa don

                            dgvThongKeDoanhThu(i + 1, 4) = New SourceGrid.Cells.Link(groupingList.Rows(i)("So").ToString)
                            dgvThongKeDoanhThu(i + 1, 4).Tag = groupingList.Rows(i)("IDDonHangBan").ToString
                            dgvThongKeDoanhThu(i + 1, 4).AddController(eventController)
                            dgvThongKeDoanhThu(i + 1, 4).View = StyleLink
                        End If
                        'dgvThongKeDoanhThu(i + 1, 4).AddController(Sour
                        dgvThongKeDoanhThu(i + 1, 5) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("SanLuong").ToString)
                        dgvThongKeDoanhThu(i + 1, 5).View = StyleRight
                        'dgvThongKeDoanhThu(i + 1, 6) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("DoanhThuCTy").ToString)
                        'dgvThongKeDoanhThu(i + 1, 6).View = StyleRight
                        dgvThongKeDoanhThu(i + 1, 6) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("NguonDoanhThu").ToString)
                        dgvThongKeDoanhThu(i + 1, 6).View = StyleLeft
                        dgvThongKeDoanhThu(i + 1, 7) = New SourceGrid.Cells.Cell(groupingList.Rows(i)("NhanVienTiepThi").ToString)
                        dgvThongKeDoanhThu(i + 1, 7).View = StyleLeft
                    End If
                Next
            Catch ex As Exception

            End Try
        End Sub

#End Region
    End Class
End Namespace